<?php
namespace Manage\Controller;
use Think\Controller;

/**
 * Class OnlyExportController
 * @package Manage\Controller
 * 只有导出方法
 */
class OnlyExportController extends Controller {

    public function station_month(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $temp_file = './excelfile/bz_month.xls';
        $pid = I('get.pid')?I('get.pid'):0;
        $start = I('get.start')?I('get.start'):'2016-01-01 00:00:00';
        $end = I('get.end')?I('get.end'):'2016-01-01 00:00:00';
        if($start >= date('Y-m-d H:i:s')){
            $start = date('Y-m-d H:i:s');
        }
        if($end > date('Y-m-d H:i:s')){
            $end = date('Y-m-d H:i:s');
        }
        $str = ' date>=\''.$start.'\' and date<=\''.$end.'\'';
        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 6;
        $row = $start_row;
        //先计算
        //提取泵站
        $station = D('PumpingStation')->queryObj($pid, 'id');
        //提取电表
        $waterele_model = D('PumpWaterele');
        $waterele_record_model = D('PumpWatereleRecord');
        $watereles = $waterele_model->queryField('ps_id', $pid);
        $waterele_array = array();
        foreach($watereles as $waterele){
            $map['eleid'] = array('eq', $waterele['id']);
            $one = $waterele_record_model->where($map)->where($str)->order('date desc')->find();
            $one['number'] = $waterele['we_name'];
            $waterele_array[] = $one;
        }
        //提取泵机
        $str = '((pump_start >= \''.$start.'\' AND pump_start <= \''.$end.'\') OR
        (pump_start <= \''.$start.'\' AND pump_off >= \''.$end.'\') OR
        (pump_off >= \''.$start.'\' AND pump_off <= \''.$end.'\')
        OR (pump_off is null AND pump_start <=  \''.$end.'\'))';
        $pump_array = array();
        $pump_model = D('PumpPump');
        $pump_record_model = D('PumpPumpRecord');
        $fys = $pump_model->queryField('ps_id',$pid);
        $map = array();
        foreach($fys as $fy){
            $map['pumppumpid'] = array('eq', $fy['id']);
            $map['status'] = array('neq', -3);
            $list = $pump_record_model->where($map)->where($str)->order('date desc')->select();
            $list = $pump_record_model->bz_record($fy, $list, $start, $end);
//            $x = array_merge($x, $list);
            //保存运行次数和运行时间
            $temp['num'] = $fy['p_number'];
            $temp['run_num'] = count($list);
            $time = 0;
            $flow = 0;
            foreach($list as $record){
                $time+=$record['time'];
                $flow+=$record['total_flow'];
            }
            $temp['run_time'] = round($time, 3);
            $temp['total_flow'] = round($flow, 2);
            $pump_array[] = $temp;
        }
        $a = 0;
        $b = 0;
        foreach($pump_array as $t){
            $a += $t['run_time'];
            $b += $t['total_flow'];
        }
        //提取格栅机
        $str = '((mac_start >= \''.$start.'\' AND mac_start <= \''.$end.'\') OR
        (mac_start <= \''.$start.'\' AND mac_off >= \''.$end.'\') OR
        (mac_off >= \''.$start.'\' AND mac_off <= \''.$end.'\')
        OR (mac_off is null AND mac_start <=  \''.$end.'\'))';
        $mac_array = array();
        $mac_model = D('PumpMachine');
        $mac_record_model = D('PumpMachineRecord');
        $fys = $mac_model->queryField('ps_id',$pid);
        $map = array();
        foreach($fys as $fy){
            $map['macid'] = array('eq', $fy['id']);
            $map['status'] = array('neq', -3);
            $list = $mac_record_model->where($map)->where($str)->order('date desc')->select();
            $list = $mac_record_model->bz_record($fy, $list, $start, $end);
            //保存运行次数和运行时间
            $temp['num'] = $fy['scrmach_number'];
            $temp['run_num'] = count($list);
            $time = 0;
            foreach($list as $record){
                $time+=$record['time'];
            }
            $temp['run_time'] = round($time, 1);
            $mac_array[] = $temp;
        }
        //本月汇总
        //泵机汇总
        $pump_total = array();
        $temp_run = 0;
        $temp_time = 0;
        $temp_flow = 0;
        foreach($pump_array as $item){
            $temp_run += $item['run_num'];
            $temp_time += $item['run_time'];
            $temp_flow += $item['total_flow'];
        }
        $pump_total['run_num'] = $temp_run;
        $pump_total['run_time'] = round($temp_time, 1);
        $pump_total['run_flow'] = round($temp_flow, 1);
        //格栅机汇总
        $mac_total = array();
        $temp_run = 0;
        $temp_time = 0;
        foreach($mac_array as $item){
            $temp_run += $item['run_num'];
            $temp_time += $item['run_time'];
        }
        $mac_total['run_num'] = $temp_run;
        $mac_total['run_time'] = round($temp_time, 1);

        //后填充
        $currentSheet->setCellValue('B3',$station['ps_name'].substr($end,0,4).'年'.substr($end,5,2).'月运行月报表');
        //写入电表
        $temp_row = 9;
        foreach($waterele_array as $item){
            $currentSheet->setCellValue('B'.$temp_row, $item['number']);
            $currentSheet->setCellValue('D'.$temp_row, $item['pump_fa']);
            $currentSheet->setCellValue('E'.$temp_row, $item['pump_pfa']);
            $currentSheet->setCellValue('F'.$temp_row, $item['pump_rta']);
            $currentSheet->setCellValue('G'.$temp_row, $item['pump_lta']);
            $currentSheet->setCellValue('H'.$temp_row, $item['pump_nfa']);
            $currentSheet->setCellValue('I'.$temp_row, $item['pump_ra']);
            if($temp_row >=11)
                $row++;
            else
                break;
        }
        //电表汇总
        //提取电表
        //照明和动力用电
        $zm_ele = 0;
        $dl_ele = 0;
        foreach($watereles as $ele){
            //提取最前和最后一条记录
            //最前一条
            $eMap['date'] = array('lt', $start);
            $eMap['eleid'] = array('eq', $ele['id']);
            $fo = $waterele_record_model->where($eMap)->field('id,eleid,pump_fa,date')->order('date desc')->find();
            //最后一条
            $eMap['date'] = array('elt', $end);
            $lo = $waterele_record_model->where($eMap)->field('id,eleid,pump_fa,date')->order('date desc')->find();
            //计算用电量:每天的用电量=（今天的正向有功值—昨天的正向有功值）*倍率
            $temp_ele = ($lo['pump_fa'] - $fo['pump_fa'])*$ele['rate'];
            if($ele['type'] == 1){
                //动力
                $dl_ele += $temp_ele;
            }else if($ele['type'] == 2){
                //照明
                $zm_ele += $temp_ele;
            }
        }
        //写入泵机运行记录
        $temp_row = 14;
        $pump_colum = array(0=>'D',1=>'E',2=>'F',3=>'G',4=>'H',5=>'I',6=>'J',7=>'K',8=>'L',9=>'M',10=>'N', 11=>'O',12=>'P',13=>'Q',14=>'R',15=>'S',16=>'T',17=>'U',18=>'V',19=>'W',20=>'X',);
        foreach($pump_array as $k => $item){
            $currentSheet->setCellValue($pump_colum[$k].$temp_row, $item['num']);
            $currentSheet->setCellValue($pump_colum[$k].($temp_row+1), $item['run_num']);
            $currentSheet->setCellValue($pump_colum[$k].($temp_row+2), round($item['run_time'], 2));
        }
        //写入格栅机
        $temp_row = 18;
        $mac_colum = array(0=>'L',1=>'M',2=>'N', 3=>'O',4=>'P',5=>'Q',6=>'R',7=>'S',8=>'T');
        foreach($mac_array as $k => $item){
            $currentSheet->setCellValue($mac_colum[$k].$temp_row, $item['num']);
            $currentSheet->setCellValue($mac_colum[$k].($temp_row+1), $item['run_num']);
            $currentSheet->setCellValue($mac_colum[$k].($temp_row+2), round($item['run_time'],2));
        }
        //写入本月汇总
        $currentSheet->setCellValue('B23', '  本月泵机运行次数:  '.$pump_total['run_num'].'  (次）  运行时间：  '.round($pump_total['run_time'],2).'  （小时）');
        $currentSheet->setCellValue('B25', '  本月格栅机运行次数:  '.$mac_total['run_num'].'  (次）  运行时间：  '.round($mac_total['run_time'],2).'  （小时）');
        $currentSheet->setCellValue('J23', '本月抽水量:  '.round($pump_total['run_flow'],2).'  （立方米）');
        $currentSheet->setCellValue('J25', '本月耗电量  动力用量:  '.$dl_ele.'  (度）  照明用量：  '.$zm_ele.'  （度）');

        //写入内容部分
        $str = '((pump_start >= \''.$start.'\' AND pump_start <= \''.$end.'\') OR
        (pump_start <= \''.$start.'\' AND pump_off >= \''.$end.'\') OR
        (pump_off >= \''.$start.'\' AND pump_off <= \''.$end.'\')
        OR (pump_off is null AND pump_start <=  \''.$end.'\'))';
        $record_model = $pump_record_model;
        $start_row = 30;
        $row = $start_row;
        //先查找出该泵站的泵机，循环遍历泵机的记录，重复写入
        $fys = $pump_model->queryField('ps_id',$pid);
        $index_num = 1;
        $fy_map = array();
        $fy_map['pumppumpid'] = array() ;
        foreach($fys as $fy){
            $fy_map['pumppumpid'][] = array('eq',$fy['id']);
        }
        if(!empty($fy_map['pumppumpid'])){
            $fy_map['pumppumpid'][] = 'OR';
        }
        $list = $record_model->where($fy_map)->where($str)->order('date asc')->select();
        $list = $record_model->bz_record_by_date($list, $start, $end);
        foreach($list as $k => $record){
            $currentSheet->setCellValue('A'.$row,$index_num++);
            $currentSheet->setCellValue('B'.$row,$record['date']);
            $currentSheet->setCellValue('C'.$row,$fy['p_number']);
            $currentSheet->setCellValue('D'.$row,$record['pump_start']);
            $currentSheet->setCellValue('E'.$row,$record['pump_off']);
            $currentSheet->setCellValue('F'.$row,round($record['time'],2));
            $currentSheet->setCellValue('G'.$row,$record['pump_start_line']);
            $currentSheet->setCellValue('H'.$row,$record['pump_off_line']);
            $currentSheet->setCellValue('I'.$row,$record['pump_electric']);
            $currentSheet->setCellValue('J'.$row,$record['pump_voltage']);
            $currentSheet->setCellValue('K'.$row,round($record['total_flow'],2));
            $currentSheet->setCellValue('L'.$row,$record['opr']);
            $row++;
        }
        //设定一级表头
        $currentSheet->mergeCells('A30:M31');
        $currentSheet->setCellValue('A30', $station['ps_name'].'泵机运行记录');
        $currentSheet->getRowDimension(1)->setRowHeight(35);
        $currentSheet->getStyle('A30')->getFont()->setName('宋体');
        $currentSheet->getStyle('A30')->getFont()->setSize(24);
        $currentSheet->getStyle('A30')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A30')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //设定二级表头
        $currentSheet->mergeCells('A32:A33');
        $currentSheet->setCellValue('A32', '序号');
        $currentSheet->mergeCells('B32:B33');
        $currentSheet->setCellValue('B32', '日期');
        $currentSheet->mergeCells('C32:C33');
        $currentSheet->setCellValue('C32', '运行机号');
        $currentSheet->mergeCells('D32:D33');
        $currentSheet->setCellValue('D32', '开机时间');
        $currentSheet->mergeCells('E32:E33');
        $currentSheet->setCellValue('E32', '关机时间');
        $currentSheet->mergeCells('F32:F33');
        $currentSheet->setCellValue('F32', '累计运行时间（小时）');
        $currentSheet->mergeCells('G32:G33');
        $currentSheet->setCellValue('G32', '开机水位(M)');
        $currentSheet->mergeCells('H32:H33');
        $currentSheet->setCellValue('H32', '关机水位(M)');
        $currentSheet->mergeCells('I32:I33');
        $currentSheet->setCellValue('I32', '运行电流(A)');
        $currentSheet->mergeCells('J32:J33');
        $currentSheet->setCellValue('J32', '运行电压(V)');
        $currentSheet->mergeCells('K32:K33');
        $currentSheet->setCellValue('K32', '抽水量(m³）');
        $currentSheet->mergeCells('L32:L33');
        $currentSheet->setCellValue('L32', '值班人员');
        $currentSheet->mergeCells('M32:M33');
        $currentSheet->setCellValue('M32', '备注');

        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getFont()->setName('宋体');
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getFont()->setSize(11);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $currentSheet->getColumnDimension('A')->setWidth(12);
        $currentSheet->getColumnDimension('B')->setWidth(12);
        $currentSheet->getColumnDimension('C')->setWidth(12);
        $currentSheet->getColumnDimension('D')->setWidth(20);
        $currentSheet->getColumnDimension('E')->setWidth(20);
        $currentSheet->getColumnDimension('F')->setWidth(12);
        $currentSheet->getColumnDimension('G')->setWidth(12);
        $currentSheet->getColumnDimension('H')->setWidth(12);
        $currentSheet->getColumnDimension('I')->setWidth(12);
        $currentSheet->getColumnDimension('J')->setWidth(12);
        $currentSheet->getColumnDimension('K')->setWidth(14);
        $currentSheet->getColumnDimension('L')->setWidth(12);
        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = $station['ps_name'].substr($end,0,4).'年'.substr($end,5,2).'月运行月报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    public function csdt_month(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $temp_file = './excelfile/bz_month.xls';
        $pid = I('get.pid')?I('get.pid'):0;
        $start = I('get.start')?I('get.start'):'2016-01-01 00:00:00';
        $end = I('get.end')?I('get.end'):'2016-01-01 00:00:00';
        if($start >= date('Y-m-d H:i:s')){
            $start = date('Y-m-d H:i:s');
        }
        if($end > date('Y-m-d H:i:s')){
            $end = date('Y-m-d H:i:s');
        }
        $str = ' date>=\''.$start.'\' and date<=\''.$end.'\'';
        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 6;
        $row = $start_row;
        //先计算
        //提取泵站
        $station = D('Csdt')->queryObj($pid, 'id');
        //提取电表水表
        $waterele_model = D('CsdtWaterele');
        $waterele_record_model = D('CsdtWatereleRecord');
        $watereles = $waterele_model->queryField( 'ps_id', $pid);
        $waterele_array = array();
        foreach($watereles as $waterele){
            $map['eleid'] = array('eq', $waterele['id']);
            $one = $waterele_record_model->where($map)->where($str)->order('date desc')->find();

            $one['number'] = $waterele['we_name'];
            $waterele_array[] = $one;
        }
        //提取泵机
        $str = '((csdt_start >= \''.$start.'\' AND csdt_start <= \''.$end.'\') OR
        (csdt_start <= \''.$start.'\' AND csdt_off >= \''.$end.'\') OR
        (csdt_off >= \''.$start.'\' AND csdt_off <= \''.$end.'\')
        OR (csdt_off is null AND csdt_start <=  \''.$end.'\'))';
        $pump_array = array();
        $pump_model = D('CsdtPump');
        $pump_record_model = D('CsdtPumpRecord');
        $fys = $pump_model->queryField('ps_id',$pid);
        $map = array();
        foreach($fys as $fy){
            $map['csdtpumpid'] = array('eq', $fy['id']);
            $map['status'] = array('neq', -3);
            $list = $pump_record_model->where($map)->where($str)->order('date desc')->select();
            $list = $pump_record_model->bz_record($fy, $list, $start, $end);
            //保存运行次数和运行时间
            $temp['num'] = $fy['p_number'];
            $temp['run_num'] = count($list);
            $time = 0;
            $flow = 0;
            foreach($list as $record){
                $time+=$record['time'];
                $flow+=$record['total_flow'];
            }
            $temp['run_time'] = round($time, 1);
            $temp['total_flow'] = round($flow, 1);
            $pump_array[] = $temp;
        }
        //提取闸门
        $str = '((gate_start >= \''.$start.'\' AND gate_start <= \''.$end.'\') OR
        (gate_start <= \''.$start.'\' AND gate_off >= \''.$end.'\') OR
        (gate_off >= \''.$start.'\' AND gate_off <= \''.$end.'\')
        OR (gate_off is null AND gate_start <=  \''.$end.'\'))';
        $gate_array = array();
        $gate_model = D('CsdtGate');
        $gate_record_model = D('CsdtGateRecord');
        $fys = $gate_model->queryField('ps_id',$pid);
        $map = array();
        foreach($fys as $fy){
            $map['gateid'] = array('eq', $fy['id']);
            $map['status'] = array('neq', -3);
            $list = $gate_record_model->where($map)->where($str)->order('date desc')->select();
            $list = $gate_record_model->bz_record($fy, $list, $start, $end);
            //保存运行次数和运行时间
            $temp['num'] = $fy['gate_name'];
            $temp['run_num'] = count($list);
            $time = 0;
            foreach($list as $record){
                $time+=$record['time'];
            }
            $temp['run_time'] = round($time, 1);
            $gate_array[] = $temp;
        }
        //提取格栅机
        $str = '((mac_start >= \''.$start.'\' AND mac_start <= \''.$end.'\') OR
        (mac_start <= \''.$start.'\' AND mac_off >= \''.$end.'\') OR
        (mac_off >= \''.$start.'\' AND mac_off <= \''.$end.'\')
        OR (mac_off is null AND mac_start <=  \''.$end.'\'))';
        $mac_array = array();
        $mac_model = D('CsdtMachine');
        $mac_record_model = D('CsdtMachineRecord');
        $fys = $mac_model->queryField('ps_id',$pid);
        $map = array();
        foreach($fys as $fy){
            $map['macid'] = array('eq', $fy['id']);
            $map['status'] = array('neq', -3);
            $list = $mac_record_model->where($map)->where($str)->order('date desc')->select();
            $list = $mac_record_model->bz_record($fy, $list, $start, $end);
            //保存运行次数和运行时间
            $temp['num'] = $fy['scrmach_number'];
            $temp['run_num'] = count($list);
            $time = 0;
            foreach($list as $record){
                $time+=$record['time'];
            }
            $temp['run_time'] = round($time, 1);
            $mac_array[] = $temp;
        }
        //本月汇总
        //泵机汇总
        $pump_total = array();
        $temp_run = 0;
        $temp_time = 0;
        $temp_flow = 0;
        foreach($pump_array as $item){
            $temp_run += $item['run_num'];
            $temp_time += $item['run_time'];
            $temp_flow += $item['total_flow'];
        }
        $pump_total['run_num'] = $temp_run;
        $pump_total['run_time'] = round($temp_time, 1);
        $pump_total['run_flow'] = round($temp_flow, 1);
        //闸门汇总
        $gate_total = array();
        $temp_run = 0;
        $temp_time = 0;
        foreach($gate_array as $item){
            $temp_run += $item['run_num'];
            $temp_time += $item['run_time'];
        }
        $gate_total['run_num'] = $temp_run;
        $gate_total['run_time'] = round($temp_time, 1);
        //格栅机汇总
        $mac_total = array();
        $temp_run = 0;
        $temp_time = 0;
        foreach($mac_array as $item){
            $temp_run += $item['run_num'];
            $temp_time += $item['run_time'];
        }
        $mac_total['run_num'] = $temp_run;
        $mac_total['run_time'] = round($temp_time, 1);
        //电表汇总
        //提取电表
        //照明和动力用电
        $zm_ele = 0;
        $dl_ele = 0;
        foreach($watereles as $ele){
            //提取最前和最后一条记录
            //最前一条
            $eMap['date'] = array('lt', $start);
            $eMap['eleid'] = array('eq', $ele['id']);
            $fo = $waterele_record_model->where($eMap)->field('id,eleid,csdt_fa,date')->order('date desc')->find();
            //最后一条
            $eMap['date'] = array('elt', $end);
            $lo = $waterele_record_model->where($eMap)->field('id,eleid,csdt_fa,date')->order('date desc')->find();
            //计算用电量:每天的用电量=（今天的正向有功值—昨天的正向有功值）*倍率
            $temp_ele = ($lo['csdt_fa'] - $fo['csdt_fa'])*$ele['rate'];
            if($ele['type'] == 1){
                //动力
                $dl_ele += $temp_ele;
            }else if($ele['type'] == 2){
                //照明
                $zm_ele += $temp_ele;
            }
        }
        //后填充
        $currentSheet->setCellValue('B3',$station['ps_name'].substr($end,0,4).'年'.substr($end,5,2).'月运行月报表');
        //写入电表
        $temp_row = 9;
        foreach($waterele_array as $item){
            $currentSheet->setCellValue('B'.$temp_row, $item['number']);
            $currentSheet->setCellValue('D'.$temp_row, $item['csdt_fa']);
            $currentSheet->setCellValue('E'.$temp_row, $item['csdt_pfa']);
            $currentSheet->setCellValue('F'.$temp_row, $item['csdt_rta']);
            $currentSheet->setCellValue('G'.$temp_row, $item['csdt_lta']);
            $currentSheet->setCellValue('H'.$temp_row, $item['csdt_nfa']);
            $currentSheet->setCellValue('I'.$temp_row, $item['csdt_ra']);
            if($temp_row >=11)
                $row++;
            else
                break;
        }
        //写入泵机运行记录
        $temp_row = 14;
        $pump_colum = array(0=>'D',1=>'E',2=>'F',3=>'G',4=>'H',5=>'I',6=>'J',7=>'K',8=>'L',9=>'M',10=>'N', 11=>'O',);
        foreach($pump_array as $k => $item){
            $currentSheet->setCellValue($pump_colum[$k].$temp_row, $item['num']);
            $currentSheet->setCellValue($pump_colum[$k].($temp_row+1), $item['run_num']);
            $currentSheet->setCellValue($pump_colum[$k].($temp_row+2), round($item['run_time'],2));
        }
        //写入闸门
        $temp_row = 18;
        $gate_colum = array(0=>'D',1=>'E',2=>'F',3=>'G');
        foreach($gate_array as $k => $item){
            $currentSheet->setCellValue($gate_colum[$k].$temp_row, $item['num']);
            $currentSheet->setCellValue($gate_colum[$k].($temp_row+1), $item['run_num']);
            $currentSheet->setCellValue($gate_colum[$k].($temp_row+2), round($item['run_time'],2));
        }
        //写入格栅机
        $temp_row = 18;
        $mac_colum = array(0=>'L',1=>'M',2=>'N', 3=>'O',);
        foreach($mac_array as $k => $item){
            $currentSheet->setCellValue($mac_colum[$k].$temp_row, $item['num']);
            $currentSheet->setCellValue($mac_colum[$k].($temp_row+1), $item['run_num']);
            $currentSheet->setCellValue($mac_colum[$k].($temp_row+2), round($item['run_time'],2));
        }
        //写入本月汇总
        $currentSheet->setCellValue('B23', '  本月泵机运行次数:  '.$pump_total['run_num'].'  (次）  运行时间：  '.round($pump_total['run_time'],2).'  （小时）');
        $currentSheet->setCellValue('B24', '  本月闸门运行次数:  '.$gate_total['run_num'].'  (次）  运行时间：  '.round($gate_total['run_time'],2).'  （小时）');
        $currentSheet->setCellValue('B25', '  本月格栅机运行次数:  '.$mac_total['run_num'].'  (次）  运行时间：  '.round($mac_total['run_time'],2).'  （小时）');
        $currentSheet->setCellValue('J23', '本月抽水量:  '.$pump_total['run_flow'].'  （立方米）');
        $currentSheet->setCellValue('J25', '本月耗电量  动力用量:  '.$dl_ele.'  (度）  照明用量：  '.$zm_ele.'  （度）');
        //写入内容部分
        $str = '((csdt_start >= \''.$start.'\' AND csdt_start <= \''.$end.'\') OR
        (csdt_start <= \''.$start.'\' AND csdt_off >= \''.$end.'\') OR
        (csdt_off >= \''.$start.'\' AND csdt_off <= \''.$end.'\')
        OR (csdt_off is null AND csdt_start <=  \''.$end.'\'))';
        $record_model = $pump_record_model;
        $start_row = 30;
        $row = $start_row;
        //先查找出该泵站的泵机，循环遍历泵机的记录，重复写入
        $fys = $pump_model->queryField('ps_id',$pid);
//        dump($fys);
//        exit;
        $index_num = 1;
        $fy_map = array();
        $fy_map['csdtpumpid'] = array() ;
        foreach($fys as $fy){
            $fy_map['csdtpumpid'][] = array('eq',$fy['id']);
        }
        if(!empty($fy_map['csdtpumpid'])){
            $fy_map['csdtpumpid'][] = 'OR';
        }
        $list = $record_model->where($fy_map)->where($str)->order('date asc')->select();
        $list = $record_model->bz_record_by_date($list, $start, $end);
        foreach($list as $k => $record){
            $currentSheet->setCellValue('A'.$row,$index_num++);
            $currentSheet->setCellValue('B'.$row,$record['date']);
            $currentSheet->setCellValue('C'.$row,$fy['p_number']);
            $currentSheet->setCellValue('D'.$row,$record['csdt_start']);
            $currentSheet->setCellValue('E'.$row,$record['csdt_off']);
            $currentSheet->setCellValue('F'.$row,round($record['time'],2));
            $currentSheet->setCellValue('G'.$row,$record['csdt_start_line']);
            $currentSheet->setCellValue('H'.$row,$record['csdt_off_line']);
            $currentSheet->setCellValue('I'.$row,$record['csdt_electric']);
            $currentSheet->setCellValue('J'.$row,$record['csdt_voltage']);
            $currentSheet->setCellValue('K'.$row,round($record['total_flow'],2));
            $currentSheet->setCellValue('L'.$row,$record['opr']);
            $row++;
        }
        //设定一级表头
        $currentSheet->mergeCells('A30:M31');
        $currentSheet->setCellValue('A30', $station['ps_name'].'泵机运行记录');
        $currentSheet->getRowDimension(1)->setRowHeight(35);
        $currentSheet->getStyle('A30')->getFont()->setName('宋体');
        $currentSheet->getStyle('A30')->getFont()->setSize(24);
        $currentSheet->getStyle('A30')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A30')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //设定二级表头
        $currentSheet->mergeCells('A32:A33');
        $currentSheet->setCellValue('A32', '序号');
        $currentSheet->mergeCells('B32:B33');
        $currentSheet->setCellValue('B32', '日期');
        $currentSheet->mergeCells('C32:C33');
        $currentSheet->setCellValue('C32', '运行机号');
        $currentSheet->mergeCells('D32:D33');
        $currentSheet->setCellValue('D32', '开机时间');
        $currentSheet->mergeCells('E32:E33');
        $currentSheet->setCellValue('E32', '关机时间');
        $currentSheet->mergeCells('F32:F33');
        $currentSheet->setCellValue('F32', '累计运行时间（小时）');
        $currentSheet->mergeCells('G32:G33');
        $currentSheet->setCellValue('G32', '开机水位(M)');
        $currentSheet->mergeCells('H32:H33');
        $currentSheet->setCellValue('H32', '关机水位(M)');
        $currentSheet->mergeCells('I32:I33');
        $currentSheet->setCellValue('I32', '运行电流(A)');
        $currentSheet->mergeCells('J32:J33');
        $currentSheet->setCellValue('J32', '运行电压(V)');
        $currentSheet->mergeCells('K32:K33');
        $currentSheet->setCellValue('K32', '抽水量(m³）');
        $currentSheet->mergeCells('L32:L33');
        $currentSheet->setCellValue('L32', '值班人员');
        $currentSheet->mergeCells('M32:M33');
        $currentSheet->setCellValue('M32', '备注');

        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getFont()->setName('宋体');
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getFont()->setSize(11);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $currentSheet->getColumnDimension('A')->setWidth(12);
        $currentSheet->getColumnDimension('B')->setWidth(12);
        $currentSheet->getColumnDimension('C')->setWidth(12);
        $currentSheet->getColumnDimension('D')->setWidth(20);
        $currentSheet->getColumnDimension('E')->setWidth(20);
        $currentSheet->getColumnDimension('F')->setWidth(12);
        $currentSheet->getColumnDimension('G')->setWidth(12);
        $currentSheet->getColumnDimension('H')->setWidth(12);
        $currentSheet->getColumnDimension('I')->setWidth(12);
        $currentSheet->getColumnDimension('J')->setWidth(12);
        $currentSheet->getColumnDimension('K')->setWidth(14);
        $currentSheet->getColumnDimension('L')->setWidth(12);
        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = $station['ps_name'].substr($end,0,4).'年'.substr($end,5,2).'月运行月报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    /***********************************     运营管理部报表        *************************************/
    /**
     * @param $start
     * @return array
     * 获取一周的日期，并返回日期数组
     */
    private function get_last_7_days($start){
        $push = array();
        $i=0;
        for($i=0;$i<8;$i++){
            $push[] = date("Y-m-d",strtotime("+".$i." day ".$start));
        }
        return $push;
    }

    private function get_day_range($start, $end){
        $push = array();
        $i=0;
        while(date("Y-m-d",strtotime("+".$i." day ".$start)) <= $end){
            $push[] = date("Y-m-d",strtotime("+".$i." day ".$start));
            $i++;
        }
        return $push;
    }

    public function pump_week_1(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $temp_file = './excelfile/blank_week.xls';
        $pid = I('get.pid')?I('get.pid'):0;
        $start = I('get.start')?I('get.start'):'2016-01-01';

        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 1;
        $row = $start_row;
        $push_array = array();
        //市区泵站
        $push_array['市区泵站抽排量（m3）'] = $this->pump_week_1_comb($start, 1);
//        exit;
        //尾水泵站
        $push_array['尾水泵站抽排量（m3）'] = $this->pump_week_1_comb($start, 2);
        //环湖截污泵站
        $push_array['环湖截污污水泵站抽排量（m3）'] = $this->pump_week_1_comb($start, 3);
        //设置表头
        $currentSheet->mergeCells( 'A1:L1');
        $currentSheet->setCellValue('A1', '泵站运行情况周报表');
        $currentSheet->getRowDimension(1)->setRowHeight(35);
        $currentSheet->getStyle('A1')->getFont()->setName('仿宋_GB2312');
        $currentSheet->getStyle('A1')->getFont()->setSize(20);
        $currentSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //设置表单格式
        $currentSheet->getColumnDimension('B')->setWidth(25);
        $ex_colum = array('C','D','E','F','G','H','I','J','K','L',);
        foreach($ex_colum as $coum){
            $currentSheet->getColumnDimension($coum)->setWidth(13);
        }
        $row++;
        //写入行
        $big_num = array('（一）','（二）','（三）');
        $index = 0;
        //日期数组
        $date_array = $this->get_last_7_days($start);
        $date_colum = array('C','D','E','F','G','H','I');
        foreach($push_array as $k => $temp_array){
            //设置二级表头
            $currentSheet->mergeCells( 'A'.$row.':L'.$row);
            $currentSheet->setCellValue('A'.$row, $big_num[$index].$k);
            $row++;
            //设定三级表头
            $currentSheet->setCellValue('A'.$row, '序号');
            $currentSheet->setCellValue('B'.$row, '泵站名称');
            $date_index = 0;
            foreach($date_array as $date_k => $temp_date){
                if($date_k >= count($date_array)-1){
                    continue;
                }
                $sub_date = substr(date("Y年m月d日", strtotime($temp_date)), 7, 10);
                $currentSheet->setCellValue($date_colum[$date_index].$row, $sub_date);
                $date_index++;
            }
            $currentSheet->setCellValue('J'.$row, '本周累计');
            $currentSheet->setCellValue('K'.$row, '本周日均');
            $currentSheet->setCellValue('L'.$row, '本年累计');
            $row++;
            //写入行
            $row_index = 1;
            foreach($temp_array as $name => $v){
                $currentSheet->setCellValue('A'.$row, $row_index);
                $currentSheet->setCellValue('B'.$row, $name);
                for($int_x=0;$int_x<count($date_colum);$int_x++){
                    $currentSheet->setCellValue($date_colum[$int_x].$row, $v[$date_array[$int_x]]['flow']);
                }
                $currentSheet->setCellValue('J'.$row, round($v['week_total']['flow'],2));
                $currentSheet->setCellValue('K'.$row, round($v['week_total_avg']['flow'],2));
                $currentSheet->setCellValue('L'.$row, round($v['year_total']['flow'],2));
                $row++;
                $row_index++;
            }
        }
        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = substr($start,0,10).'泵站运行情况周报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    private function pump_week_1_comb($start, $cat){
        $station_model = D('PumpingStation');
        $fy_model = D('PumpPump');
        $record_model = D('PumpPumpRecord');
        //日期数组
        $date_array = $this->get_last_7_days($start);
        //提取泵站
        if(!S('ps_catg3_'.$cat)){
            S('ps_catg3_'.$cat, json_encode($station_model->queryField('ps_catg3', $cat)));
        }
        $city_station = json_decode(S('ps_catg3_'.$cat), true);
        $city_array = array();
        foreach($city_station as $station){
            $city_array[$station['ps_name']] = array();
            //提取泵机
            if(!S('ps_pump_'.$station['id'])){
                S('ps_pump_'.$station['id'], json_encode($fy_model->queryField('ps_id', $station['id'])));
            }
            $fys = json_decode(S('ps_pump_'.$station['id']), true);
            //按照日期来提取记录
            $week_flow = 0;
//            dump($date_array);
            foreach($date_array as $k => $date){
                if($k >= count($date_array)-1){
                    continue;
                }
                $temp_start = $date.' 00:00:00';
                $temp_end = $date_array[$k+1].' 00:00:00';
                if($temp_end > date('Y-m-d H:i:s')){
                    $temp_end = date('Y-m-d H:i:s');
                }
                $str = '((pump_start >= \''.$temp_start.'\' AND pump_start <= \''.$temp_end.'\') OR
        (pump_start <= \''.$temp_start.'\' AND pump_off >= \''.$temp_end.'\') OR
        (pump_off >= \''.$temp_start.'\' AND pump_off <= \''.$temp_end.'\')
        OR (pump_off is null AND pump_start <=  \''.$temp_end.'\'))';
                //提取泵机的所有满足条件的记录
                $flow = 0;
                foreach($fys as $fy){
                    $map['pumppumpid'] = array('eq', $fy['id']);
//                    $map['ischecked'] = array('eq', 1);
                    $map['status'] = array('neq', -3);
                    $temp_records = $record_model->where($map)->where($str)->select();
                    $flow += $record_model->week_record($fy, $temp_start, $temp_end, $temp_records);
					$temp_records = null;
					$fy = null;
					$map = null;
                }
                if($temp_start >= date('Y-m-d H:i:s')){
                    $week_flow = 0;
                    $flow = 0;
                }
                $week_flow += $flow;
                $city_array[$station['ps_name']][$date]['date'] = $date;
                $city_array[$station['ps_name']][$date]['flow'] = round($flow, 2)?round($flow, 2):'';
            }
            //本周累计
            $city_array[$station['ps_name']]['week_total']['flow'] = round($week_flow, 2)?round($week_flow, 2):'';
            //本周日均
            $city_array[$station['ps_name']]['week_total_avg']['flow'] = round(($week_flow/7), 2)?round(($week_flow/7), 2):'';
            //本年累计
            $str = '((pump_start >= \''.substr($start,0,4).'-01-01 00:00:00\' AND pump_start <= \''.substr($start,0,4).'-12-31 23:59:59\') OR
        (pump_start <= \''.substr($start,0,4).'-01-01 00:00:00\' AND pump_off >= \''.substr($start,0,4).'-12-31 23:59:59\') OR
        (pump_off >= \''.substr($start,0,4).'-01-01 00:00:00\' AND pump_off <= \''.substr($start,0,4).'-12-31 23:59:59\')
        OR (pump_off is null AND pump_start <=  \''.$temp_end.'-12-31 23:59:59\'))';
//            $str = ' date>=\''.substr($start,0,4).'-01-01 00:00:00\' and date<=\''.substr($start,0,4).'-12-31 23:59:59\'';
            $year_flow = 0;
            foreach($fys as $fy){
                $map['pumppumpid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
                $temp_records = $record_model->where($map)->where($str)->select();
                $year_flow += $record_model->week_record($fy, substr($date,0,4).'-01-01 00:00:00', substr($date,0,4).'-12-31 23:59:59', $temp_records);
				$temp_records = null;
                $fy = null;
                $map = null;
            }
            $city_array[$station['ps_name']]['year_total']['flow'] = round($year_flow, 2)?round($year_flow, 2):'';
			$station = null;
        }
		$city_station = null;
        return $city_array;
    }

    public function pump_week_2(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        //源文件
        $temp_file = './excelfile/blank_week2.xls';
        $pid = I('get.pid')?I('get.pid'):0;
        $start = I('get.start')?I('get.start'):'2016-01-01';

        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 1;
        $row = $start_row;
        //市区泵站
        $push_array = $this->pump_week_2_comb($start);
//        dump($push_array);
        //设置表头
        $currentSheet->mergeCells( 'A1:G1');
        $currentSheet->setCellValue('A1', '泵站运行情况周报表');
        $currentSheet->getRowDimension(1)->setRowHeight(35);
        $currentSheet->getStyle('A1')->getFont()->setName('仿宋_GB2312');
        $currentSheet->getStyle('A1')->getFont()->setSize(20);
        $currentSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //设置表单格式
        //增加单元格宽度
        $ex_colum = array('C','D','E');
        foreach($ex_colum as $coum){
            $currentSheet->getColumnDimension($coum)->setWidth(18);
        }
        $currentSheet->getColumnDimension('F')->setWidth(25);
        $row++;
        //写入行
        $index = 1;
        //日期数组
        $date_array = $this->get_last_7_days($start);
        $date_colum = array('B', 'C','D','E','F');
        foreach($push_array as $k => $temp_array){
            //设置二级表头
            $currentSheet->mergeCells( 'A'.$row.':G'.$row);
            $currentSheet->setCellValue('A'.$row, $index.'、'.$k);
            $currentSheet->getStyle('A'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $currentSheet->getStyle('A'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $index++;
            $row++;
            //设定三级表头
            $currentSheet->setCellValue('A'.$row, '序号');
            $currentSheet->setCellValue('B'.$row, '日期');
            $currentSheet->setCellValue('C'.$row, '抽排量（m3)');
            $currentSheet->setCellValue('D'.$row, '泵机运行台数（台');
            $currentSheet->setCellValue('E'.$row, '运行时间(h）');
            $currentSheet->setCellValue('F'.$row, '泵机抽排能力（m3/台•h）');
            $currentSheet->setCellValue('G'.$row, '备注');
            $row++;
            //写入行
            $row_index = 1;
            //这里就是循环日期层了
            foreach($temp_array as $date => $v){
                if($row_index <= 7){
                    $currentSheet->setCellValue('A'.$row, $row_index);
                    $currentSheet->setCellValue('B'.$row, $date);
                    for($int_x=0;$int_x<count($date_colum);$int_x++){
                        $currentSheet->setCellValue($date_colum[$int_x].$row, $v[$date_array[$int_x]]['flow']);
                    }
//                    $currentSheet->setCellValue('J'.$row, round($v['week_total']['flow'],2));
//                    $currentSheet->setCellValue('K'.$row, round($v['week_total_avg']['flow'],2));
//                    $currentSheet->setCellValue('L'.$row, round($v['year_total']['flow'],2));
                    //从C列开始写入，最后再写ABG列
                    $day_run_num = 0;
                    $day_run_time = 0;
                    $day_num_flow = 0;
                    if($v['child']){
                        $i = 0;
                        foreach($v['child'] as $flow => $child){
                            $day_run_num += $child['run_num'];
                            $day_run_time += $child['run_time'];
                            $day_num_flow += $child['run_flow'];
                            $currentSheet->setCellValue('C'.($row+$i), round($child['run_flow'], 2));
                            $currentSheet->setCellValue('D'.($row+$i), $child['run_num']);
                            $currentSheet->setCellValue('E'.($row+$i), round($child['run_time'],2));
                            $currentSheet->setCellValue('F'.($row+$i), $flow);
                            $i++;
                        }
                    }
                    //AG列要多加一行，作为小计行
                    $currentSheet->mergeCells( 'A'.$row.':A'.($row+1+count($v['child'])));
                    $currentSheet->setCellValue('A'.$row, $row_index);
                    $currentSheet->mergeCells( 'B'.$row.':B'.($row+count($v['child'])));
                    $currentSheet->setCellValue('B'.$row, substr(date("Y年m月d日", strtotime($date)), 7, 10));
                    $currentSheet->mergeCells( 'G'.$row.':G'.($row+1+count($v['child'])));
                    //当日小计行
                    $currentSheet->setCellValue('B'.($row+1+count($v['child'])), '小计');
                    $currentSheet->setCellValue('C'.($row+1+count($v['child'])), round($day_num_flow,2));
                    $currentSheet->setCellValue('D'.($row+1+count($v['child'])), $day_run_num);
                    $currentSheet->setCellValue('E'.($row+1+count($v['child'])), round($day_run_time,2));
                    $row = $row+2+count($v['child']);
                    $row_index++;
                }else{
                    //本周累计、本年累计、本周日均
//                    dump($temp_array['week_total']);
                    $currentSheet->mergeCells( 'A'.$row.':B'.$row);
                    $currentSheet->setCellValue('A'.$row, '本周合计');
                    $currentSheet->setCellValue('C'.$row, round($temp_array['week_total']['flow'],2));
                    $currentSheet->setCellValue('E'.$row, round($temp_array['week_total_time']['time'],2));
                    $currentSheet->mergeCells( 'A'.($row+1).':B'.($row+1));
                    $currentSheet->setCellValue('A'.($row+1), '本年累计');
                    $currentSheet->setCellValue('C'.($row+1), round($temp_array['year_total']['flow'],2));
                    $currentSheet->mergeCells( 'A'.($row+2).':B'.($row+2));
                    $currentSheet->setCellValue('A'.($row+2), '本周日均');
                    $currentSheet->setCellValue('C'.($row+2), round($temp_array['week_total_avg']['flow'],2));
                    $row += 3;
                    break;
                }
            }

        }
        $currentSheet->getStyle('A1:'.'G'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = substr($start,0,10).'泵站运行情况周报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    public function pump_week_2_comb($start){
        $station_model = D('PumpingStation');
        $fy_model = D('PumpPump');
        $record_model = D('PumpPumpRecord');
        //日期数组
        $date_array = $this->get_last_7_days($start);
        //提取泵站
        $city_station = $station_model->select(I('get.ids'));
        $city_array = array();
        foreach($city_station as $station){
            $city_array[$station['ps_name']] = array();
            //提取泵机
            $fys = $fy_model->queryField('ps_id', $station['id']);
            //按照日期来提取记录
            $week_flow = 0;
            $week_time = 0;
            foreach($date_array as $k => $date){
                if($k >= count($date_array) - 1){
                    continue;
                }
                $temp_start = $date.' 00:00:00';
                $temp_end = $date_array[$k+1].' 00:00:00';
                if($temp_end >= date('Y-m-d H:i:s')){
                    $temp_end = date('Y-m-d H:i:s');
                }
                $str = '((pump_start >= \''.$temp_start.'\' AND pump_start <= \''.$temp_end.'\') OR
        (pump_start <= \''.$temp_start.'\' AND pump_off >= \''.$temp_end.'\') OR
        (pump_off >= \''.$temp_start.'\' AND pump_off <= \''.$temp_end.'\')
        OR (pump_off is null AND pump_start <=  \''.$temp_end.'\'))';
                //提取泵机的所有满足条件的记录
                foreach($fys as $fy){
                    $map['pumppumpid'] = array('eq', $fy['id']);
//                    $map['ischecked'] = array('eq', 1);
                    $map['status'] = array('neq', -3);
                    $temp_records = $record_model->where($map)->where($str)->select();
//                    echo $record_model->getLastSql();
                    $obj = $record_model->week2_record($fy, $date.' 00:00:00', $date.' 23:59:59', $temp_records);
                    //使用单位抽排量来做键
                    //只记录运行的
                    if($city_array[$station['ps_name']][$date]['child'][$fy['p_flow_meas']])
                        $date_flow_array = $city_array[$station['ps_name']][$date]['child'][$fy['p_flow_meas']];
                    else
                        $date_flow_array = array();
                    $date_flow_array['run_num'] = $obj['time']?($date_flow_array['run_num'] + 1):($date_flow_array['run_num'] + 0);
                    $date_flow_array['run_time'] += $obj['time'];
                    $date_flow_array['run_flow'] += $obj['flow'];
                    $date_flow_array['flow'] += $fy['p_flow_meas'];
                    $city_array[$station['ps_name']][$date]['child'][$fy['p_flow_meas']] = $date_flow_array;
                    $week_flow += $obj['flow'];
                    $week_time += $obj['time'];
                }
                if($temp_start >= date('Y-m-d H:i:s')){
                    $week_flow = 0;
                    $week_time = 0;
                }
                $city_array[$station['ps_name']][$date]['date'] = $date;
                $city_array[$station['ps_name']][$date]['flow'] = round($obj['flow'], 2)?round($obj['flow'], 2):'';

            }
            //本周累计
            $city_array[$station['ps_name']]['week_total']['flow'] = round($week_flow, 2)?round($week_flow, 2):'';
            //本周日均
            $city_array[$station['ps_name']]['week_total_avg']['flow'] = round(($week_flow/7), 2)?round(($week_flow/7), 2):'';
            //本周累计时间
            $city_array[$station['ps_name']]['week_total_time']['time'] = round($week_time, 2)?round($week_time, 2):'';
            //本年累计
//            $str = ' date>=\''.substr($start,0,4).'-01-01 00:00:00\' and date<=\''.substr($start,0,4).'-12-31 23:59:59\'';
            $str = '((pump_start >= \''.substr($start,0,4).'-01-01 00:00:00\' AND pump_start <= \''.substr($start,0,4).'-12-31 23:59:59\') OR
        (pump_start <= \''.substr($start,0,4).'-01-01 00:00:00\' AND pump_off >= \''.substr($start,0,4).'-12-31 23:59:59\') OR
        (pump_off >= \''.substr($start,0,4).'-01-01 00:00:00\' AND pump_off <= \''.substr($start,0,4).'-12-31 23:59:59\')
        OR (pump_off is null AND pump_start <=  \''.$temp_end.'-12-31 23:59:59\'))';
            $year_flow = 0;
            foreach($fys as $fy){
                $map['pumppumpid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
                $map['status'] = array('neq', -3);
                $temp_records = $record_model->where($map)->where($str)->select();
                $year_flow += $record_model->week_record($fy, substr($date,0,4).'-01-01 00:00:00', substr($date,0,4).'-12-31 23:59:59', $temp_records);
            }
            $city_array[$station['ps_name']]['year_total']['flow'] = round($year_flow, 2)?round($year_flow, 2):'';
        }
//        exit;
        return $city_array;
    }

    public function csdt_week(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        //源文件
        $temp_file = './excelfile/blank_week3.xls';
        $start = I('get.start')?I('get.start'):date('Y-m-d');
        $end = I('get.end')?I('get.end'):date('Y-m-d');
        if($start >= date('Y-m-d H:i:s')){
            $start = date('Y-m-d H:i:s');
        }
        if($end >= date('Y-m-d H:i:s')){
            $end = date('Y-m-d H:i:s');
        }
        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 1;
        $row = $start_row;
        //日期数组
        $date_array = $this->get_day_range($start, $end);
        //提取出组合
        $push_array = $this->csdt_week_comb($this->csdt_week_pump_comb($start, $end), $this->csdt_week_gate_comb($start, $end));
        $ex_colum = array('C','D','E','F','G','H','I','J','K','L',);
        foreach($ex_colum as $coum){
            $currentSheet->getColumnDimension($coum)->setWidth(14);
        }
        $currentSheet->getColumnDimension('L')->setWidth(15);
        //设定一级表头
        $currentSheet->mergeCells( 'A1:M1');
        $currentSheet->setCellValue('A1', '调蓄池运行情况周报表');
        $currentSheet->getRowDimension(1)->setRowHeight(35);
        $currentSheet->getStyle('A1')->getFont()->setName('仿宋_GB2312');
        $currentSheet->getStyle('A1')->getFont()->setSize(20);
//        $currentSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//        $currentSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $row++;
        $index = 1;
        foreach($push_array as $station_name => $temp_array){
            //设定二级表头
            $currentSheet->mergeCells( 'A'.$row.':M'.$row);
            $currentSheet->setCellValue('A'.$row, $index.'、'.$station_name);
            $row++;
            $index++;
            //设定三级表头
            $currentSheet->mergeCells( 'A'.$row.':A'.($row+1));
            $currentSheet->setCellValue('A'.$row, '序号');
            $currentSheet->mergeCells( 'B'.$row.':B'.($row+1));
            $currentSheet->setCellValue('B'.$row, '日期');
            $currentSheet->mergeCells( 'C'.$row.':F'.$row);
            $currentSheet->setCellValue('C'.$row, '闸门运行情况');
            $currentSheet->setCellValue('C'.($row+1), '开闸时间');
            $currentSheet->setCellValue('D'.($row+1), '关闸时间');
            $currentSheet->setCellValue('E'.($row+1), '运行时间（h）');
            $currentSheet->setCellValue('F'.($row+1), '闸门运行次数');
            $currentSheet->mergeCells( 'G'.$row.':L'.$row);
            $currentSheet->setCellValue('G'.$row, '泵机运行情况');
            $currentSheet->setCellValue('G'.($row+1), '抽排量(m³)');
            $currentSheet->setCellValue('H'.($row+1), '运行台数（台）');
            $currentSheet->setCellValue('I'.($row+1), '开机时间');
            $currentSheet->setCellValue('J'.($row+1), '关机时间');
            $currentSheet->setCellValue('K'.($row+1), '运行时间（h');
            $currentSheet->setCellValue('L'.($row+1), '抽排能力(m³/台•h)');
            $currentSheet->mergeCells( 'M'.$row.':M'.($row+1));
            $currentSheet->setCellValue('M'.$row, '备注');
            $row+=2;
            //写入内容
            $temp_gate = $temp_array['gate'];
            $temp_pump = $temp_array['pump'];
            $week_index = 1;
            foreach($date_array as $temp_date){
                $currentSheet->setCellValue('A'.$row, $week_index);
                $week_index++;
                $currentSheet->setCellValue('B'.$row, substr(date("Y年m月d日", strtotime($temp_date)), 7, 10));
                $date_gate = $temp_gate[$temp_date];
                $date_pump = $temp_pump[$temp_date];
                //写入闸门
                $currentSheet->setCellValue('C'.$row, $date_gate['start']?substr($date_gate['start'], 11, 5):'');
                $currentSheet->setCellValue('D'.$row, $date_gate['end']?substr($date_gate['end'], 11, 5):'');
                $currentSheet->setCellValue('E'.$row, round($date_gate['run_time'],2));
                $currentSheet->setCellValue('F'.$row, $date_gate['run_num']);
                //写入泵机
                $run_num = 0;
                $run_time = 0;
                $run_flow = 0;
                $flow_str = '';
                foreach($date_pump['child'] as $pumpL){
                    $run_num += $pumpL['run_num'];
                    $run_time += $pumpL['run_time'];
                    $run_flow += $pumpL['run_flow'];
                    $flow_str .= $pumpL['flow'].'、';
                }
                $currentSheet->setCellValue('G'.$row, round($run_flow,2));
                $currentSheet->setCellValue('H'.$row, $run_num);
                $currentSheet->setCellValue('K'.$row, round($run_time,2));
                //抽排能力
                $currentSheet->setCellValue('L'.$row, $flow_str);
                $currentSheet->setCellValue('I'.$row, $date_pump['start']?substr($date_pump['start'], 11, 5):'');
                $currentSheet->setCellValue('J'.$row, $date_pump['end']?substr($date_pump['end'], 11, 5):'');
                $row++;
            }
            //每个泵站的小计
            $currentSheet->mergeCells( 'A'.$row.':B'.$row);
            $currentSheet->setCellValue('A'.$row, '本周合计');
            $currentSheet->setCellValue('E'.$row, round($temp_gate['week_total_time']['time'],2));
            $currentSheet->setCellValue('F'.$row, $temp_gate['week_total']['run_num']);
            $currentSheet->setCellValue('G'.$row, round($temp_pump['week_total']['flow'],2));
            $currentSheet->setCellValue('K'.$row, round($temp_pump['week_total_time']['time'],2));
            $row++;
            $currentSheet->mergeCells( 'A'.$row.':B'.$row);
            $currentSheet->setCellValue('A'.$row, '本年累计');
            $currentSheet->setCellValue('E'.$row, round($temp_gate['year_total']['time'],2));
            $currentSheet->setCellValue('F'.$row, $temp_gate['year_total']['run_num']);
            $currentSheet->setCellValue('G'.$row, round($temp_pump['year_total']['flow'],2));
            $currentSheet->setCellValue('K'.$row, round($temp_pump['year_total']['time'],2));
            $row++;
        }
        $row--;
        $currentSheet->getStyle('A1:M'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A1:M'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $currentSheet->getStyle('A1:'.'M'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);

        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = substr($start,0,10).'调蓄池运行情况周报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    public function  csdt_week_pump_comb($start, $end){
        $station_model = D('Csdt');
        $fy_model = D('CsdtPump');
        $record_model = D('CsdtPumpRecord');
        //日期数组
        $date_array = $this->get_day_range($start, $end);
        //提取泵站
        $city_station = $station_model->select(I('get.ids'));
        $city_array = array();
        foreach($city_station as $station){
            $city_array[$station['ps_name']] = array();
            //提取泵机
            $fys = $fy_model->queryField('ps_id', $station['id']);
            //按照日期来提取记录
            $week_flow = 0;
            $week_time = 0;
            foreach($date_array as $date){
                $temp_start = $date.' 00:00:00';
                $temp_end = $date.' 23:59:59';
                if($temp_end >= date('Y-m-d H:i:s')){
                    $temp_end = date('Y-m-d H:i:s');
                }
                $str = '((csdt_start >= \''.$temp_start.'\' AND csdt_start <= \''.$temp_end.'\') OR
                (csdt_start <= \''.$temp_start.'\' AND csdt_off >= \''.$temp_end.'\') OR
                (csdt_off >= \''.$temp_start.'\' AND csdt_off <= \''.$temp_end.'\')
                OR (csdt_off is null AND csdt_start <=  \''.$temp_end.'\'))';
                //提取泵机的所有满足条件的记录
                $first_start = '9999-12-31 23:59:59';
                $last_end = '';
                foreach($fys as $fy){
                    $map['csdtpumpid'] = array('eq', $fy['id']);
//                    $map['ischecked'] = array('eq', 1);
                    $map['status'] = array('neq', -3);
                    $temp_records = $record_model->where($map)->where($str)->order('date asc')->select();
                    $obj = $record_model->week2_record($fy, $date.' 00:00:00', $date.' 23:59:59', $temp_records);
//                    echo $record_model->getLastSql();
                    //使用单位抽排量来做键
                    //只记录运行的
                    if($city_array[$station['ps_name']][$date]['child'][$fy['p_flow_meas']])
                        $date_flow_array = $city_array[$station['ps_name']][$date]['child'][$fy['p_flow_meas']];
                    else
                        $date_flow_array = array();
                    $date_flow_array['run_num'] = $obj['time']?($date_flow_array['run_num'] + 1):($date_flow_array['run_num'] + 0);
                    $date_flow_array['run_time'] += $obj['time'];
                    $date_flow_array['run_flow'] += $obj['flow'];
                    $date_flow_array['flow'] = $fy['p_flow_meas'];//泵机单位抽排量
                    $city_array[$station['ps_name']][$date]['child'][$fy['p_flow_meas']] = $date_flow_array;

//                    $city_array[$station['ps_name']][$date]['run_num'] = $obj['time']?($city_array[$station['ps_name']][$date]['run_num'] + 1):($city_array[$station['ps_name']][$date]['run_num'] + 0);
//                    $city_array[$station['ps_name']][$date]['run_time'] += $obj['time'];
                    $week_flow += $obj['flow'];
                    $week_time += $obj['time'];
                    if($obj['start'] <= $first_start&&$obj['start']>'0000-00-00 00:00:00'){
                        $first_start = $obj['start'];
                        $city_array[$station['ps_name']][$date]['start'] = $obj['start'];
                    }

                    if($obj['end'] >= $last_end){
                        $last_end = $obj['end'];
                        $city_array[$station['ps_name']][$date]['end'] = $obj['end'];
                    }
                }
                if($temp_start >= date('Y-m-d H:i:s')){
                    $week_flow = 0;
                    $week_time = 0;
                }
                $city_array[$station['ps_name']][$date]['date'] = $date;
                $city_array[$station['ps_name']][$date]['flow'] = round($obj['flow'], 2)?round($obj['flow'], 2):'';

            }
            //本周累计
            $city_array[$station['ps_name']]['week_total']['flow'] = round($week_flow, 2)?round($week_flow, 2):'';
            //本周日均
            $city_array[$station['ps_name']]['week_total_avg']['flow'] = round(($week_flow/7), 2)?round(($week_flow/7), 2):'';
            //本周累计时间
            $city_array[$station['ps_name']]['week_total_time']['time'] = round($week_time, 2)?round($week_time, 2):'';
            //本年累计
//            $str = ' date>=\''.substr($start,0,4).'-01-01 00:00:00\' and date<=\''.substr($start,0,4).'-12-31 23:59:59\'';

            $str = '((csdt_start >= \''.substr($start,0,4).'-01-01 00:00:00\' AND csdt_start <= \''.substr($start,0,4).'-12-31 23:59:59\') OR
                (csdt_start <= \''.substr($start,0,4).'-01-01 00:00:00\' AND csdt_off >= \''.substr($start,0,4).'-12-31 23:59:59\') OR
                (csdt_off >= \''.substr($start,0,4).'-01-01 00:00:00\' AND csdt_off <= \''.substr($start,0,4).'-12-31 23:59:59\')
                OR (csdt_off is null AND csdt_start <=  \''.substr($start,0,4).'-12-31 23:59:59\'))';
            $year_flow = 0;
            $year_time = 0;
            foreach($fys as $fy){
                $map['csdtpumpid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
                $map['status'] = array('neq', -3);
                $temp_records = $record_model->where($map)->where($str)->select();
                $temp_record = $record_model->week_record($fy, substr($date,0,4).'-01-01 00:00:00', substr($date,0,4).'-12-31 23:59:59', $temp_records);
                $year_flow += $temp_record['flow'];
                $year_time += $temp_record['time'];
            }
            $city_array[$station['ps_name']]['year_total']['flow'] = round($year_flow, 2)?round($year_flow, 2):'';
            $city_array[$station['ps_name']]['year_total']['time'] = round($year_time, 2)?round($year_time, 2):'';
        }
        return $city_array;
    }

    public function csdt_week_gate_comb($start, $end){
        $station_model = D('Csdt');
        $fy_model = D('CsdtGate');
        $record_model = D('CsdtGateRecord');
        //日期数组
        $date_array = $this->get_day_range($start, $end);
        //提取泵站
        $city_station = $station_model->select(I('get.ids'));
        $city_array = array();
        foreach($city_station as $station){
            $city_array[$station['ps_name']] = array();
            //提取泵机
            $fys = $fy_model->queryField('ps_id', $station['id']);
            //按照日期来提取记录
            $week_run_time = 0;
            $week_time = 0;
            foreach($date_array as $date){
                $temp_start = $date.' 00:00:00';
                $temp_end = $date.' 23:59:59';
                if($temp_end >= date('Y-m-d H:i:s')){
                    $temp_end = date('Y-m-d H:i:s');
                }
                $str = '((gate_start >= \''.$temp_start.'\' AND gate_start <= \''.$temp_end.'\') OR
                (gate_start <= \''.$temp_start.'\' AND gate_off >= \''.$temp_end.'\') OR
                (gate_off >= \''.$temp_start.'\' AND gate_off <= \''.$temp_end.'\')
                OR (gate_off is null AND gate_start <=  \''.$temp_end.'\'))';
                //提取泵机的所有满足条件的记录
                $first_start = '9999-12-31 23:59:59';
                $last_end = '';
                foreach($fys as $fy){
                    $map['gateid'] = array('eq', $fy['id']);
//                    $map['ischecked'] = array('eq', 1);
                    $map['status'] = array('neq', -3);
                    $temp_records = $record_model->where($map)->where($str)->select();
                    $obj = $record_model->week2_record($fy, $date.' 00:00:00', $date.' 23:59:59', $temp_records);
                    //使用单位抽排量来做键
                    //只记录运行的

                    $city_array[$station['ps_name']][$date]['run_num'] = $obj['time']?($city_array[$station['ps_name']][$date]['run_num'] + 1):($city_array[$station['ps_name']][$date]['run_num'] + 0);
                    $city_array[$station['ps_name']][$date]['run_time'] += $obj['time'];
                    if($obj['start'] <= $first_start&&$obj['start']>'0000-00-00 00:00:00'){
                        $first_start = $obj['start'];
                        $city_array[$station['ps_name']][$date]['start'] = $obj['start'];
                    }
                    if($obj['end'] >= $last_end){
                        $last_end = $obj['end'];
                        $city_array[$station['ps_name']][$date]['end'] = $obj['end'];
                    }
                    $week_time += $obj['time'];
                    $week_run_time += $obj['run_num'];
                }
                if($temp_start >= date('Y-m-d H:i:s')){
                    $week_time = 0;
                    $week_run_time = 0;
                }
                $city_array[$station['ps_name']][$date]['date'] = $date;

            }
            //本周累计
            $city_array[$station['ps_name']]['week_total']['run_num'] = $week_run_time;
            //本周累计时间
            $city_array[$station['ps_name']]['week_total_time']['time'] = round($week_time, 2)?round($week_time, 2):'';
            //本年累计
//            $str = ' date>=\''.substr($start,0,4).'-01-01 00:00:00\' and date<=\''.substr($start,0,4).'-12-31 23:59:59\'';

            $str = '((gate_start >= \''.substr($start,0,4).'-01-01 00:00:00\' AND gate_start <= \''.substr($start,0,4).'-12-31 23:59:59\') OR
                (gate_start <= \''.substr($start,0,4).'-01-01 00:00:00\' AND gate_off >= \''.substr($start,0,4).'-12-31 23:59:59\') OR
                (gate_off >= \''.substr($start,0,4).'-01-01 00:00:00\' AND gate_off <= \''.substr($start,0,4).'-12-31 23:59:59\')
                OR (gate_off is null AND gate_start <=  \''.substr($start,0,4).'-12-31 23:59:59\'))';
            $year_run_num = 0;
            $year_run_time = 0;
            foreach($fys as $fy){
                $map['gateid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
                $temp_records = $record_model->where($map)->where($str)->select();
                $temp_record = $record_model->week_record($fy, substr($date,0,4).'-01-01 00:00:00', substr($date,0,4).'-12-31 23:59:59', $temp_records);
                $year_run_num += $temp_record['run_num'];
                $year_run_time += $temp_record['time'];
            }
            $city_array[$station['ps_name']]['year_total']['time'] = round($year_run_time, 2)?round($year_run_time, 2):'';
            $city_array[$station['ps_name']]['year_total']['run_num'] = $year_run_num;
        }
        return $city_array;
    }

    private function csdt_week_comb($pump, $gate){
        //遍历调蓄池
        $station_model = D('Csdt');
        $stations = $station_model->select(I('get.ids'));
        $return_array = array();
        foreach($stations as $station){
            //根据调蓄池，设定键值对
            $temp_array = $return_array[$station['ps_name']] = array();
            //提取出对应的闸门
            $temp_gate = $gate[$station['ps_name']];
            //提取出对应的泵机
            $temp_pump = $pump[$station['ps_name']];
            $return_array[$station['ps_name']]['gate'] = $temp_gate;
            $return_array[$station['ps_name']]['pump'] = $temp_pump;

        }
        return $return_array;
    }

    /**
     * 运营管理部泵站日报表
     */
    public function pump_day(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        //源文件
        $temp_file = './excelfile/yy_day.xls';
        $ids = I('get.ids')?I('get.ids'):0;
        $start = I('get.start')?I('get.start'):date('Y-m-d');

        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $station_model = D('PumpingStation');
        $php_excel = $php_reader->load($filename);
        //生成多个sheet
        $temp_stations = explode(',', I('get.ids'));
        $index = 0;
        foreach($temp_stations as $station_id){
            $station = $station_model->queryObj($station_id, 'id');
            $new_sheet = new \PHPExcel_Worksheet($php_excel); //创建一个工作表
            $php_excel->addSheet($new_sheet); //插入工作表
            $php_excel->setActiveSheetIndex($index);
            $currentSheet = $php_excel->getActiveSheet($index);
            $currentSheet->setTitle($station['ps_name']);
            $start_row = 4;
            $row = $start_row;
            //先组合出一年的日期
            $year_date = $this->comb_year_days($start);
            $push = $this->pump_day_comb($station, $year_date);
            $extend_colum = array('D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV',);

            //动态生成列
            $colum_index = 0;
            foreach($push['pumps'] as $pump){
                $currentSheet->getColumnDimension($extend_colum[$colum_index])->setWidth(14);
                $currentSheet->getColumnDimension($extend_colum[$colum_index+1])->setWidth(14);
                $currentSheet->mergeCells( $extend_colum[$colum_index].$row.':'.$extend_colum[$colum_index+1].$row);
                $currentSheet->setCellValue($extend_colum[$colum_index].$row, $pump['p_number'].'('.pump_pump_type_option($pump['type'], false).')'.'/'.$pump['p_flow_meas'].'m³/h');
                $currentSheet->setCellValue($extend_colum[$colum_index].($row+1), '运行时间（h)');
                $currentSheet->setCellValue($extend_colum[$colum_index+1].($row+1), '抽排量m³');
                $colum_index += 2;

            }
            $currentSheet->getColumnDimension($extend_colum[$colum_index])->setWidth(16);
            $currentSheet->mergeCells( $extend_colum[$colum_index].$row.':'.$extend_colum[$colum_index].($row+1));
            $currentSheet->setCellValue($extend_colum[$colum_index].$row, '合计运行时间（h)');
            $colum_index += 1;
            $currentSheet->getColumnDimension($extend_colum[$colum_index])->setWidth(16);
            $currentSheet->mergeCells( $extend_colum[$colum_index].$row.':'.$extend_colum[$colum_index].($row+1));
            $currentSheet->setCellValue($extend_colum[$colum_index].$row, '合计抽排量（m³）');
            $row += 2;
            $station_name = '';
            foreach($push['city_array'] as $station_name => $temp_array){
                $station_name = $station_name;
                //遍历泵机情况，然后写入
                foreach($temp_array as $every_day){
                    $colum_index = 0;
                    $total_time = 0;
                    $total_flow = 0;
                    foreach($every_day['child'] as $pump_qingkuang){
                        $total_time += $pump_qingkuang['run_time'];
                        $total_flow += $pump_qingkuang['run_flow'];
                        $currentSheet->setCellValue($extend_colum[$colum_index].$row, round($pump_qingkuang['run_time'],2));
                        $colum_index++;
                        $currentSheet->setCellValue($extend_colum[$colum_index].$row, round($pump_qingkuang['run_flow'],2));
                        $colum_index++;
                    }
                    $currentSheet->setCellValue($extend_colum[$colum_index].$row, round($total_time,2));
                    $colum_index++;
                    $currentSheet->setCellValue($extend_colum[$colum_index].$row, round($total_flow,2));
                    $colum_index++;
                    $row++;
                }
            }
            $colum_index--;
            //设定一级表头
            $currentSheet->mergeCells('A1:'.$extend_colum[$colum_index].'1');
            $currentSheet->setCellValue('A1', '泵站泵机日运行情况报表');
            $currentSheet->getRowDimension(1)->setRowHeight(35);
            $currentSheet->getStyle('A1')->getFont()->setName('仿宋_GB2312');
            $currentSheet->getStyle('A1')->getFont()->setSize(20);
            $currentSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $currentSheet->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //设定二级表头
            $currentSheet->mergeCells('A2:'.$extend_colum[$colum_index].'3');
            $currentSheet->setCellValue('A2', $station_name.substr($start, 0, 4).'年');
            $currentSheet->getRowDimension(1)->setRowHeight(35);
            $currentSheet->getStyle('A2')->getFont()->setName('宋体');
            $currentSheet->getStyle('A2')->getFont()->setSize(20);
            $currentSheet->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $currentSheet->getStyle('A2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            //设定左侧日期
            $this->write_yy_day_left($currentSheet, $start);
            //设定三级表头
            $currentSheet->mergeCells('A4:A5');
            $currentSheet->setCellValue('A4', '年');
            $currentSheet->mergeCells('B4:B5');
            $currentSheet->setCellValue('B4', '月');
            $currentSheet->mergeCells('C4:C5');
            $currentSheet->setCellValue('C4', '日');
            $colum_index = $colum_index-2;
            $currentSheet->getStyle('A1:'.$extend_colum[$colum_index+2].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $currentSheet->getStyle('A1:'.$extend_colum[$colum_index+2].$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $currentSheet->getStyle('A1:'.$extend_colum[$colum_index+2].$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $index++;
        }


        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = substr($start,0,10).'泵站泵机日运行情况报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    /**
     * @param $start组合出一年的365天
     */
    private function comb_year_days($start){
        $year = substr($start, 0, 4);
        $return_array = array();
        for($month=1;$month<13;$month++){
            for($day=1;$day<32;$day++){
                $return_array[] = $year.'-'.str_pad($month,2,'0',STR_PAD_LEFT).'-'.str_pad($day,2,'0',STR_PAD_LEFT);
            }
        }
        return $return_array;
    }

    private function write_yy_day_left($currentSheet, $start){
        $row = 6;
//        $last_row = 6+12*31;
        //设定年
        $year = substr($start, 0, 4);
        for($month=1;$month<13;$month++){
            $currentSheet->mergeCells('A'.$row.':A'.($row+30));
            $currentSheet->setCellValue('A'.$row,  substr($start, 0, 4));
            //设置月
            $currentSheet->mergeCells('B'.$row.':B'.($row+30));
            $currentSheet->setCellValue('B'.$row,  $month);
            for($day=1;$day<32;$day++){
                $currentSheet->setCellValue('C'.$row,  $day);
                $row++;
            }
//            $row++;
        }
    }

    /***
     * @param $start
     * @param $pump_type  泵机类型
     * @param $year_date_array  一年365天的日期数组
     * 每次只做一个泵站，因为数据量太大了
     */
    public function pump_day_comb($station, $year_date_array){
        $most_pump_num = 0;
        $pumps = array();

        $fy_model = D('PumpPump');
        $record_model = D('PumpPumpRecord');
        //日期数组
        $date_array = $year_date_array;
        $city_array = array();
        $city_array[$station['ps_name']] = array();
        //提取泵机
        $fys = array();
        $x_m['ps_id'] = array('eq', $station['id']);
//        if(I('get.rain') == 'true'){
//            $x_m['type'] = array('eq', 1);
//            $fys = array_merge($fys,  $fy_model->where($x_m)->select());
//        }
//        if(I('get.dran') == 'true'){
//            $x_m['type'] = array('eq', 2);
//            $fys = array_merge($fys,  $fy_model->where($x_m)->select());
//        }
        $fys = $fy_model->where($x_m)->select();
        $pumps = array_merge($pumps, $fys);
        //统计泵机数量，用于动态生成列
        if(count($fys) > $most_pump_num){
            $most_pump_num = count($fys);
        }
        //按照日期来提取记录
        $week_flow = 0;
        $week_time = 0;
        foreach($date_array as $date){
            $temp_start = $date.' 00:00:00';
            $temp_end = $date.' 23:59:59';
            $str = '((pump_start >= \''.$temp_start.'\' AND pump_start <= \''.$temp_end.'\') OR
                (pump_start <= \''.$temp_start.'\' AND pump_off >= \''.$temp_end.'\') OR
                (pump_off >= \''.$temp_start.'\' AND pump_off <= \''.$temp_end.'\')
                OR (pump_off is null AND pump_start <=  \''.$temp_end.'\'))';
            //提取泵机的所有满足条件的记录
            foreach($fys as $fy){
                $map['pumppumpid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
                $map['status'] = array('neq', -3);
                $temp_records = $record_model->where($map)->where($str)->select();
                $obj = $record_model->week2_record($fy, $date.' 00:00:00', $date.' 23:59:59', $temp_records);
                //使用单位抽排量来做键
                //只记录运行的
                if($city_array[$station['ps_name']][$date]['child'][$fy['p_number']])
                    $date_flow_array = $city_array[$station['ps_name']][$date]['child'][$fy['p_number']];
                else
                    $date_flow_array = array();
                $date_flow_array['run_num'] = $obj['time']?($date_flow_array['run_num'] + 1):($date_flow_array['run_num'] + 0);
                $date_flow_array['run_time'] += $obj['time'];
                $date_flow_array['run_flow'] += $obj['flow'];
                $date_flow_array['flow'] += $fy['p_flow_meas'];
                $city_array[$station['ps_name']][$date]['child'][$fy['p_number']] = $date_flow_array;
                $week_flow += $obj['flow'];
                $week_time += $obj['time'];
            }
            $city_array[$station['ps_name']][$date]['date'] = $date;
            $city_array[$station['ps_name']][$date]['flow'] = round($obj['flow'], 2)?round($obj['flow'], 2):'';

        }
//            //本周累计
//            $city_array[$station['ps_name']]['week_total']['flow'] = round($week_flow, 2)?round($week_flow, 2):'';
//            //本周日均
//            $city_array[$station['ps_name']]['week_total_avg']['flow'] = round(($week_flow/7), 2)?round(($week_flow/7), 2):'';
//            //本周累计时间
//            $city_array[$station['ps_name']]['week_total_time']['time'] = round($week_time, 2)?round($week_time, 2):'';
//            //本年累计
//            $str = ' date>=\''.substr($start,0,4).'-01-01 00:00:00\' and date<=\''.substr($start,0,4).'-12-31 23:59:59\'';
//            $year_flow = 0;
//            foreach($fys as $fy){
//                $map['pumppumpid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
//                $temp_records = $record_model->where($map)->where($str)->select();
//                $year_flow += $record_model->week_record($fy, substr($date,0,4).'-01-01 00:00:00', substr($date,0,4).'-12-31 23:59:59', $temp_records);
//            }
//            $city_array[$station['ps_name']]['year_total']['flow'] = round($year_flow, 2)?round($year_flow, 2):'';
        return array('most_pump_num'=>$most_pump_num, 'pumps'=>$pumps, 'city_array'=>$city_array);
    }

    /**
     * 泵站月报表。总表
     */
    public function station_month_total(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $temp_file = './excelfile/blank_week4.xls';
        $s = $start = I('get.start')?I('get.start').' 00:00:00':'2016-01-01 00:00:00';
        $e = $end = I('get.end')?I('get.end').' 23:59:59':'2016-01-01 00:00:00';
        if($start > date('Y-m-d H:i:s')){
            $start = date('Y-m-d H:i:s');
        }
        if($end > date('Y-m-d H:i:s')){
            $end = date('Y-m-d H:i:s');
        }
        $str = '((pump_start >= \''.$start.'\' AND pump_start <= \''.$end.'\') OR
                (pump_start <= \''.$start.'\' AND pump_off >= \''.$end.'\') OR
                (pump_off >= \''.$start.'\' AND pump_off <= \''.$end.'\')
                OR (pump_off is null AND pump_start <=  \''.$end.'\'))';
        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 20;
        $row = $start_row;
        //先计算
        //提取泵站
        if(!S('pumpstation')){
            S('pumpstation', json_encode(D('PumpingStation')->getAll()));
        }
        $stations = json_decode(S('pumpstation'), true);
        $push = array();
        $pump_model = D('PumpPump');
        $pump_record_model = D('PumpPumpRecord');
        $ele_model = D('PumpWaterele');
        $ele_record_model = D('PumpWatereleRecord');
        foreach($stations as $station){
            $push[$station['ps_name']] = array();
            $pid = $station['id'];
            //提取泵机
            $fys = $pump_model->queryField('ps_id',$pid);
            $map = array();
            $time = 0;
            $flow = 0;
            $run_num = 0;
            foreach($fys as $fy){
                $map['pumppumpid'] = array('eq', $fy['id']);
                $map['status'] = array('neq', -3);
                $list = $pump_record_model->where($map)->where($str)->order('date desc')->select();
                $list = $pump_record_model->bz_record($fy, $list, $start, $end);
                //保存运行次数和运行时间
                $run_num += count($list);
                foreach($list as $record){
                    $time+=$record['time'];
                    $flow+=$record['total_flow'];
                }
            }
            $time = round($time, 2);
            $flow = round($flow, 2);
            $push[$station['ps_name']]['run_num'] = $run_num;
            $push[$station['ps_name']]['time'] = $time;
            $push[$station['ps_name']]['flow'] = $flow;
            //提取电表
            $eles = $ele_model->queryField('ps_id',$pid);
            //照明和动力用电
            $zm_ele = 0;
            $dl_ele = 0;
            foreach($eles as $ele){
                //提取最前和最后一条记录
                //最前一条
                $eMap['date'] = array('lt', $start);
                $eMap['eleid'] = array('eq', $ele['id']);
                $fo = $ele_record_model->where($eMap)->field('id,eleid,pump_fa,date')->order('date desc')->find();
                //最后一条
                $eMap['date'] = array('elt', $end);
                $lo = $ele_record_model->where($eMap)->field('id,eleid,pump_fa,date')->order('date desc')->find();
                //计算用电量:每天的用电量=（今天的正向有功值—昨天的正向有功值）*倍率
                $temp_ele = ($lo['pump_fa'] - $fo['pump_fa'])*$ele['rate'];
                if($ele['type'] == 1){
                    //动力
                    $dl_ele += $temp_ele;
                }else if($ele['type'] == 2){
                    //照明
                    $zm_ele += $temp_ele;
                }
                if($ele['type'] == 2){
                    if(($lo || $fo)){
//                        dump($ele);
//                        dump($lo);
//                        dump($fo);
//                        break;
                    }
                }
            }
            $push[$station['ps_name']]['dl_ele'] = $dl_ele;
            $push[$station['ps_name']]['zm_ele'] = $zm_ele;
        }
        //设定一级表头
        $currentSheet->mergeCells('A'.$row.':A'.($row+1));
        $currentSheet->setCellValue('A'.$row, '序号');
        $currentSheet->mergeCells('B'.$row.':B'.($row+1));
        $currentSheet->setCellValue('B'.$row, '泵站名称');
        $currentSheet->mergeCells('C'.$row.':C'.($row+1));
        $currentSheet->setCellValue('C'.$row, '开泵次数(次)');
        $currentSheet->mergeCells('D'.$row.':D'.($row+1));
        $currentSheet->setCellValue('D'.$row, '运行时间    (小时)');
        $currentSheet->mergeCells('E'.$row.':E'.($row+1));
        $currentSheet->setCellValue('E'.$row, '抽水量(m3)');
        $currentSheet->mergeCells('F'.$row.':G'.$row);
        $currentSheet->setCellValue('F'.$row, '耗用动力电');
        $currentSheet->setCellValue('F'.($row+1), '电量(度)');
        $currentSheet->setCellValue('G'.($row+1), '电费(元)');
        $currentSheet->mergeCells('H'.$row.':H'.($row+1));
        $currentSheet->setCellValue('H'.$row, '单位电耗(度/千方)');
        $currentSheet->mergeCells('I'.$row.':J'.$row);
        $currentSheet->setCellValue('I'.$row, '耗用照明电');
        $currentSheet->setCellValue('I'.($row+1), '电量(度)');
        $currentSheet->setCellValue('J'.($row+1), '电费(元)');
        $currentSheet->mergeCells('K'.$row.':L'.$row);
        $currentSheet->setCellValue('K'.$row, '耗用自来水');
        $currentSheet->setCellValue('K'.($row+1), '水量(吨)');
        $currentSheet->setCellValue('L'.($row+1), '水费(元)');
        $currentSheet->mergeCells('M'.$row.':M'.($row+1));
        $currentSheet->setCellValue('M'.$row, '月运行费(元)');
        $row += 2;
        //写入记录
        $index_num = 1;
        $run_num = 0;
        $time = 0;
        $flow = 0;
        $dl_ele = 0;
        $zm_ele = 0;
        foreach($push as $name => $record){
            $currentSheet->setCellValue('A'.$row, $index_num++);
            $currentSheet->setCellValue('B'.$row, $name);
            $currentSheet->setCellValue('C'.$row, $record['run_num']);
            $currentSheet->setCellValue('D'.$row, $record['time']);
            $currentSheet->setCellValue('E'.$row, $record['flow']);
            $currentSheet->setCellValue('F'.$row, $record['dl_ele']);
            $currentSheet->setCellValue('I'.$row, $record['zm_ele']);
            $run_num += $record['run_num'];
            $time += $record['time'];
            $flow += $record['flow'];
            $dl_ele += $record['dl_ele'];
            $zm_ele += $record['zm_ele'];
            $row++;
        }
        //本月合计
        $month_flow = $flow;
        $month_ele = $dl_ele+$zm_ele;
        $currentSheet->setCellValue('B'.$row, '本月合计');
        $currentSheet->setCellValue('C'.$row, $run_num);
        $currentSheet->setCellValue('D'.$row, $time);
        $currentSheet->setCellValue('E'.$row, $flow);
        $currentSheet->setCellValue('F'.$row, $dl_ele);
        $currentSheet->setCellValue('I'.$row, $zm_ele);
        $currentSheet->getStyle('A1:M'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A1:M'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $row+=2;

        $start = substr($start,0,4).'-01-01 00:00:00';
        $end = substr($start,0,4).'-12-31 23:59:59';
        $str = '((pump_start >= \''.$start.'\' AND pump_start <= \''.$end.'\') OR
            (pump_start <= \''.$start.'\' AND pump_off >= \''.$end.'\') OR
            (pump_off >= \''.$start.'\' AND pump_off <= \''.$end.'\')
            OR (pump_off is null AND pump_start <=  \''.$end.'\'))';
        //先计算
        //提取泵站
        $stations = D('PumpingStation')->getAll();
        $push = array();
        foreach($stations as $station){
            $push[$station['ps_name']] = array();
            $pid = $station['id'];
            //提取泵机
            $fys = $pump_model->queryField('ps_id',$pid);
            $map = array();
            $time = 0;
            $flow = 0;
            $run_num = 0;
            foreach($fys as $fy){
                $map['pumppumpid'] = array('eq', $fy['id']);
                $map['status'] = array('neq', -3);
                $list = $pump_record_model->where($map)->where($str)->order('date desc')->select();
                $list = $pump_record_model->bz_record($fy, $list, $start, $end);
                //保存运行次数和运行时间
                $run_num += count($list);
                foreach($list as $record){
                    $time+=$record['time'];
                    $flow+=$record['total_flow'];
                }
            }
            $time = round($time, 2);
            $flow = round($flow, 2);
            $push[$station['ps_name']]['run_num'] = $run_num;
            $push[$station['ps_name']]['time'] = $time;
            $push[$station['ps_name']]['flow'] = $flow;
            //提取电表
            $eles = $ele_model->queryField('ps_id',$pid);
            //照明和动力用电
            $zm_ele = 0;
            $dl_ele = 0;
            foreach($eles as $ele){
                //提取最前和最后一条记录
                //最前一条
                $eMap['date'] = array('lt', $start);
                $eMap['eleid'] = array('eq', $ele['id']);
                $fo = $ele_record_model->where($eMap)->field('id,eleid,pump_fa,date')->order('date desc')->find();
                //最后一条
                $eMap['date'] = array('elt', $end);
                $lo = $ele_record_model->where($eMap)->field('id,eleid,pump_fa,date')->order('date desc')->find();
                //计算用电量:每天的用电量=（今天的正向有功值—昨天的正向有功值）*倍率
                $temp_ele = ($lo['pump_fa'] - $fo['pump_fa'])*$ele['rate'];
                if($ele['type'] == 1){
                    //动力
                    $dl_ele += $temp_ele;
                }else if($ele['type'] == 2){
                    //照明
                    $zm_ele += $temp_ele;
                }
            }
            $push[$station['ps_name']]['dl_ele'] = $dl_ele;
            $push[$station['ps_name']]['zm_ele'] = $zm_ele;
        }
        //设定一级表头
        $currentSheet->mergeCells('A'.$row.':A'.($row+1));
        $currentSheet->setCellValue('A'.$row, '序号');
        $currentSheet->mergeCells('B'.$row.':B'.($row+1));
        $currentSheet->setCellValue('B'.$row, '泵站名称');
        $currentSheet->mergeCells('C'.$row.':C'.($row+1));
        $currentSheet->setCellValue('C'.$row, '累计开泵次数(次)');
        $currentSheet->mergeCells('D'.$row.':D'.($row+1));
        $currentSheet->setCellValue('D'.$row, '累计运行时间    (小时)');
        $currentSheet->mergeCells('E'.$row.':E'.($row+1));
        $currentSheet->setCellValue('E'.$row, '累计抽水量(m3)');
        $currentSheet->mergeCells('F'.$row.':G'.$row);
        $currentSheet->setCellValue('F'.$row, '累计耗用动力电');
        $currentSheet->setCellValue('F'.($row+1), '电量(度)');
        $currentSheet->setCellValue('G'.($row+1), '电费(元)');
        $currentSheet->mergeCells('H'.$row.':H'.($row+1));
        $currentSheet->setCellValue('H'.$row, '累计单位电耗(度/千方)');
        $currentSheet->mergeCells('I'.$row.':J'.$row);
        $currentSheet->setCellValue('I'.$row, '累计耗用照明电');
        $currentSheet->setCellValue('I'.($row+1), '电量(度)');
        $currentSheet->setCellValue('J'.($row+1), '电费(元)');
        $currentSheet->mergeCells('K'.$row.':L'.$row);
        $currentSheet->setCellValue('K'.$row, '累计耗用自来水');
        $currentSheet->setCellValue('K'.($row+1), '水量(吨)');
        $currentSheet->setCellValue('L'.($row+1), '水费(元)');
        $currentSheet->mergeCells('M'.$row.':M'.($row+1));
        $currentSheet->setCellValue('M'.$row, '累计月运行费(元)');
        $row += 2;
        //写入记录
        $index_num = 1;
        $run_num = 0;
        $time = 0;
        $flow = 0;
        $dl_ele = 0;
        $zm_ele = 0;
        foreach($push as $name => $record){
            $currentSheet->setCellValue('A'.$row, $index_num++);
            $currentSheet->setCellValue('B'.$row, $name);
            $currentSheet->setCellValue('C'.$row, $record['run_num']);
            $currentSheet->setCellValue('D'.$row, $record['time']);
            $currentSheet->setCellValue('E'.$row, $record['flow']);
            $currentSheet->setCellValue('F'.$row, $record['dl_ele']);
            $currentSheet->setCellValue('I'.$row, $record['zm_ele']);
            $run_num += $record['run_num'];
            $time += $record['time'];
            $flow += $record['flow'];
            $dl_ele += $record['dl_ele'];
            $zm_ele += $record['zm_ele'];
            $row++;
        }
        //本年合计
        $year_ele = $dl_ele + $zm_ele;
        $currentSheet->setCellValue('B'.$row, '本年合计');
        $currentSheet->setCellValue('C'.$row, $run_num);
        $currentSheet->setCellValue('D'.$row, $time);
        $currentSheet->setCellValue('E'.$row, $flow);
        $currentSheet->setCellValue('E'.$row, $flow);
        $currentSheet->setCellValue('F'.$row, $dl_ele);
        $currentSheet->setCellValue('I'.$row, $zm_ele);
        //设定封面
        $currentSheet->setCellValue('C7', date('Y年m月d日', strtotime($s)).'至'.date('Y年m月d日', strtotime($e)));
        $currentSheet->setCellValue('K7', $month_flow);
        $currentSheet->setCellValue('K11', $flow);
        $currentSheet->setCellValue('K8', $month_ele);
        $currentSheet->setCellValue('K12', $year_ele);
        $currentSheet->setCellValue('J18', getNowDate_zhcn());
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $currentSheet->getStyle('A'.$start_row.':M'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = substr($start,0,4).'年'.substr($this_month,5,2).'月泵站月运行月报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }

    /**
     * 调蓄池月报表。总表
     */
    public function csdt_month_total(){
        header("Content-type: text/html; charset=utf-8");
        import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Reader.Excel5");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");
        $temp_file = './excelfile/blank_week3.xls';
        $s = $start = I('get.start')?I('get.start').' 00:00:00':'2016-01-01 00:00:00';
        $e = $end = I('get.end')?I('get.end').' 23:59:59':'2016-01-01 00:00:00';
        $this_month = I('get.start')?I('get.start'):'2016-01-01';
        if($start >= date('Y-m-d H:i:s')){
            $start = date('Y-m-d H:i:s');
        }
        if($end > date('Y-m-d H:i:s')){
            $end = date('Y-m-d H:i:s');
        }
        $php_reader = new \PHPExcel_Reader_Excel5();
        $filename = file_zh_cn_icvon($temp_file);
        if(!$php_reader->canRead($filename)){
            echo '无法识别的Excel文件！';
            return false;
        }
        $php_excel = $php_reader->load($filename);
        $currentSheet = $php_excel->getSheet(0);
        $start_row = 22;
        $row = $start_row;
        //先计算
        //提取泵站
        $stations = D('Csdt')->getAll();
        $push = array();
        $pump_model = D('CsdtPump');
        $pump_record_model = D('CsdtPumpRecord');
        $gate_model = D('CsdtGate');
        $gate_record_model = D('CsdtGateRecord');
        $ele_model = D('CsdtWaterele');
        $ele_record_model = D('CsdtWatereleRecord');
        foreach($stations as $station){
            $push[$station['ps_name']] = array();
            $pid = $station['id'];
            //提取泵机
            $fys = $pump_model->queryField('ps_id',$pid);
            $map = array();
            $time = 0;
            $flow = 0;
            $run_num = 0;
            $save_water_num = 0;
            $save_water_time = 0;
            $str = '((csdt_start >= \''.$start.'\' AND csdt_start <= \''.$end.'\') OR
            (csdt_start <= \''.$start.'\' AND csdt_off >= \''.$end.'\') OR
            (csdt_off >= \''.$start.'\' AND csdt_off <= \''.$end.'\')
            OR (csdt_off is null AND csdt_start <=  \''.$end.'\'))';
            foreach($fys as $fy){
                $map['csdtpumpid'] = array('eq', $fy['id']);
                $map['status'] = array('neq', -3);
                $list = $pump_record_model->where($map)->where($str)->order('date desc')->select();
                $list = $pump_record_model->bz_record($fy, $list, $start, $end);
                //保存运行次数和运行时间
                $run_num += count($list);
                foreach($list as $record){
                    $time+=$record['time'];
                    $flow+=$record['total_flow'];
                }
            }
            $time = round($time, 2);
            $flow = round($flow, 2);
            $push[$station['ps_name']]['run_num'] = $run_num;
            $push[$station['ps_name']]['time'] = $time;
            $push[$station['ps_name']]['flow'] = $flow;
            //提取闸门
            $str = '((gate_start > \''.$start.'\' AND gate_start < \''.$end.'\') OR
            (gate_start < \''.$start.'\' AND gate_off > \''.$end.'\') OR
            (gate_off > \''.$start.'\' AND gate_off < \''.$end.'\')
            OR (gate_off is null AND gate_start <=  \''.$end.'\'))';
            $fys = $gate_model->queryField('ps_id',$pid);
            $map = array();
            foreach($fys as $fy){
                $map['gateid'] = array('eq', $fy['id']);
//                $map['ischecked'] = array('eq', 1);
                $map['status'] = array('neq', -3);
                $list = $gate_record_model->where($map)->where($str)->order('date desc')->select();
                $list = $gate_record_model->bz_record($fy, $list, $start, $end);
                //保存运行次数和运行时间
                $save_water_num += count($list);
                foreach($list as $record){
                    $save_water_time += $record['time'];
                }
                $save_water_time = round($save_water_time, 2);
                $push[$station['ps_name']]['save_water_num'] = $save_water_num;
                $push[$station['ps_name']]['save_water_time'] = $save_water_time;
            }
            //提取电表
            $eles = $ele_model->queryField('ps_id',$pid);
            //照明和动力用电
            $zm_ele = 0;
            $dl_ele = 0;
            foreach($eles as $ele){
                //提取最前和最后一条记录
                //最前一条
                $eMap['date'] = array('lt', $start);
                $eMap['eleid'] = array('eq', $ele['id']);
                $fo = $ele_record_model->where($eMap)->field('id,eleid,csdt_fa,date')->order('date desc')->find();
                //最后一条
                $eMap['date'] = array('elt', $end);
                $lo = $ele_record_model->where($eMap)->field('id,eleid,csdt_fa,date')->order('date desc')->find();
                //计算用电量:每天的用电量=（今天的正向有功值—昨天的正向有功值）*倍率
                $temp_ele = ($lo['csdt_fa'] - $fo['csdt_fa'])*$ele['rate'];
                if($ele['type'] == 1){
                    //动力
                    $dl_ele += $temp_ele;
                }else if($ele['type'] == 2){
                    //照明
                    $zm_ele += $temp_ele;
                }
            }
            $push[$station['ps_name']]['dl_ele'] = $dl_ele;
            $push[$station['ps_name']]['zm_ele'] = $zm_ele;
        }
        //设定一级表头
        $currentSheet->mergeCells('A'.$row.':A'.($row+1));
        $currentSheet->setCellValue('A'.$row, '序号');
        $currentSheet->mergeCells('B'.$row.':B'.($row+1));
        $currentSheet->setCellValue('B'.$row, '调蓄池名称');
        $currentSheet->mergeCells('C'.$row.':C'.($row+1));
        $currentSheet->setCellValue('C'.$row, '蓄水次数(次)');
        $currentSheet->mergeCells('D'.$row.':D'.($row+1));
        $currentSheet->setCellValue('D'.$row, '蓄水时间（小时）');
        $currentSheet->mergeCells('E'.$row.':E'.($row+1));
        $currentSheet->setCellValue('E'.$row, '开泵次数(次)');
        $currentSheet->mergeCells('F'.$row.':F'.($row+1));
        $currentSheet->setCellValue('F'.$row, '运行时间    (小时)');
        $currentSheet->mergeCells('G'.$row.':G'.($row+1));
        $currentSheet->setCellValue('G'.$row, '抽水量(m3)');
        $currentSheet->mergeCells('H'.$row.':I'.$row);
        $currentSheet->setCellValue('H'.$row, '耗用动力电');
        $currentSheet->setCellValue('H'.($row+1), '电量(度)');
        $currentSheet->setCellValue('I'.($row+1), '电费(元)');
        $currentSheet->mergeCells('J'.$row.':J'.($row+1));
        $currentSheet->setCellValue('J'.$row, '单位电耗(度/千方)');
        $currentSheet->mergeCells('K'.$row.':L'.$row);
        $currentSheet->setCellValue('K'.$row, '耗用照明电');
        $currentSheet->setCellValue('K'.($row+1), '电量(度)');
        $currentSheet->setCellValue('L'.($row+1), '电费(元)');
        $currentSheet->mergeCells('M'.$row.':N'.$row);
        $currentSheet->setCellValue('M'.$row, '耗用自来水');
        $currentSheet->setCellValue('M'.($row+1), '水量(吨)');
        $currentSheet->setCellValue('N'.($row+1), '水费(元)');
        $currentSheet->mergeCells('O'.$row.':O'.($row+1));
        $currentSheet->setCellValue('O'.$row, '月运行费(元)');
        $row += 2;
        //写入记录
        $index_num = 1;
        $run_num = 0;
        $time = 0;
        $flow = 0;
        $save_water_num = 0;
        $save_water_time = 0;
        $dl_ele = 0;
        $zm_ele = 0;
        foreach($push as $name => $record){
            $currentSheet->setCellValue('A'.$row, $index_num++);
            $currentSheet->setCellValue('B'.$row, $name);
            $currentSheet->setCellValue('C'.$row, $record['save_water_num']);
            $currentSheet->setCellValue('D'.$row, $record['save_water_time']);
            $currentSheet->setCellValue('E'.$row, $record['run_num']);
            $currentSheet->setCellValue('F'.$row, $record['time']);
            $currentSheet->setCellValue('G'.$row, $record['flow']);
            $currentSheet->setCellValue('H'.$row, $record['dl_ele']);
            $currentSheet->setCellValue('K'.$row, $record['zm_ele']);
            $run_num += $record['run_num'];
            $time += $record['time'];
            $flow += $record['flow'];
            $save_water_num += $record['save_water_num'];
            $save_water_time += $record['save_water_time'];
            $dl_ele += $record['dl_ele'];
            $zm_ele += $record['zm_ele'];
            $row++;
        }
        //本月合计
        $month_flwo = $flow;
        $month_ele = $dl_ele+$zm_ele;
        $currentSheet->setCellValue('B'.$row, '本月合计');
        $currentSheet->setCellValue('C'.$row, $save_water_num);
        $currentSheet->setCellValue('D'.$row, $save_water_time);
        $currentSheet->setCellValue('E'.$row, $run_num);
        $currentSheet->setCellValue('F'.$row, $time);
        $currentSheet->setCellValue('G'.$row, $flow);
        $currentSheet->setCellValue('H'.$row, $dl_ele);
        $currentSheet->setCellValue('K'.$row, $zm_ele);
        $row+=2;
        $start = substr($start,0,4).'-01-01 00:00:00';
        $end = substr($start,0,4).'-12-31 00:00:00';
        //先计算
        $push = array();
        foreach($stations as $station){
            $push[$station['ps_name']] = array();
            $pid = $station['id'];
            //提取泵机
            $str = '((csdt_start >= \''.$start.'\' AND csdt_start <= \''.$end.'\') OR
            (csdt_start <= \''.$start.'\' AND csdt_off >= \''.$end.'\') OR
            (csdt_off >= \''.$start.'\' AND csdt_off <= \''.$end.'\')
            OR (csdt_off is null AND csdt_start <=  \''.$end.'\'))';
            $fys = $pump_model->queryField('ps_id',$pid);
            $map = array();
            $time = 0;
            $flow = 0;
            $run_num = 0;
            $save_water_num = 0;
            $save_water_time = 0;
            foreach($fys as $fy){
                $map['csdtpumpid'] = array('eq', $fy['id']);
                $map['status'] = array('neq', -3);
                $list = $pump_record_model->where($map)->where($str)->order('date desc')->select();
                $list = $pump_record_model->bz_record($fy, $list, $start, $end);
                //保存运行次数和运行时间
                $run_num += count($list);
                foreach($list as $record){
                    $time+=$record['time'];
                    $flow+=$record['total_flow'];
                }
            }
            $time = round($time, 2);
            $flow = round($flow, 2);
            $push[$station['ps_name']]['run_num'] = $run_num;
            $push[$station['ps_name']]['time'] = $time;
            $push[$station['ps_name']]['flow'] = $flow;
            //提取闸门
            $str = '((gate_start > \''.$start.'\' AND gate_start < \''.$end.'\') OR
            (gate_start < \''.$start.'\' AND gate_off > \''.$end.'\') OR
            (gate_off > \''.$start.'\' AND gate_off < \''.$end.'\')
            OR (gate_off is null AND gate_start <=  \''.$end.'\'))';
            $fys = $gate_model->queryField('ps_id',$pid);
            $map = array();
            foreach($fys as $fy){
                $map['gateid'] = array('eq', $fy['id']);
                $map['status'] = array('neq', -3);
                $list = $gate_record_model->where($map)->where($str)->order('date desc')->select();
                $list = $gate_record_model->bz_record($fy, $list, $start, $end);
                //保存运行次数和运行时间
                $save_water_num += count($list);
                foreach($list as $record){
                    $save_water_time += $record['time'];
                }
                $save_water_time = round($save_water_time, 2);
                $push[$station['ps_name']]['save_water_num'] = $save_water_num;
                $push[$station['ps_name']]['save_water_time'] = $save_water_time;
            }
            //提取电表
            $eles = $ele_model->queryField('ps_id',$pid);
            //照明和动力用电
            $zm_ele = 0;
            $dl_ele = 0;
            foreach($eles as $ele){
                //提取最前和最后一条记录
                //最前一条
                $eMap['date'] = array('lt', $start);
                $eMap['eleid'] = array('eq', $ele['id']);
                $fo = $ele_record_model->where($eMap)->field('id,eleid,csdt_fa,date')->order('date desc')->find();
                //最后一条
                $eMap['date'] = array('elt', $end);
                $lo = $ele_record_model->where($eMap)->field('id,eleid,csdt_fa,date')->order('date desc')->find();
                //计算用电量:每天的用电量=（今天的正向有功值—昨天的正向有功值）*倍率
                $temp_ele = ($lo['csdt_fa'] - $fo['csdt_fa'])*$ele['rate'];
                if($ele['type'] == 1){
                    //动力
                    $dl_ele += $temp_ele;
                }else if($ele['type'] == 2){
                    //照明
                    $zm_ele += $temp_ele;
                }
            }
            $push[$station['ps_name']]['dl_ele'] = $dl_ele;
            $push[$station['ps_name']]['zm_ele'] = $zm_ele;
        }
        //设定一级表头
        $currentSheet->mergeCells('A'.$row.':A'.($row+1));
        $currentSheet->setCellValue('A'.$row, '序号');
        $currentSheet->mergeCells('B'.$row.':B'.($row+1));
        $currentSheet->setCellValue('B'.$row, '调蓄池名称');
        $currentSheet->mergeCells('C'.$row.':C'.($row+1));
        $currentSheet->setCellValue('C'.$row, '累计蓄水次数(次)');
        $currentSheet->mergeCells('D'.$row.':D'.($row+1));
        $currentSheet->setCellValue('D'.$row, '累计蓄水时间（小时）');
        $currentSheet->mergeCells('E'.$row.':E'.($row+1));
        $currentSheet->setCellValue('E'.$row, '累计开泵次数(次)');
        $currentSheet->mergeCells('F'.$row.':F'.($row+1));
        $currentSheet->setCellValue('F'.$row, '累计运行时间    (小时)');
        $currentSheet->mergeCells('G'.$row.':G'.($row+1));
        $currentSheet->setCellValue('G'.$row, '累计抽水量(m3)');
        $currentSheet->mergeCells('H'.$row.':I'.$row);
        $currentSheet->setCellValue('H'.$row, '累计耗用动力电');
        $currentSheet->setCellValue('H'.($row+1), '电量(度)');
        $currentSheet->setCellValue('I'.($row+1), '电费(元)');
        $currentSheet->mergeCells('J'.$row.':J'.($row+1));
        $currentSheet->setCellValue('J'.$row, '累计单位电耗(度/千方)');
        $currentSheet->mergeCells('K'.$row.':L'.$row);
        $currentSheet->setCellValue('K'.$row, '累计耗用照明电');
        $currentSheet->setCellValue('K'.($row+1), '电量(度)');
        $currentSheet->setCellValue('L'.($row+1), '电费(元)');
        $currentSheet->mergeCells('M'.$row.':N'.$row);
        $currentSheet->setCellValue('M'.$row, '累计耗用自来水');
        $currentSheet->setCellValue('M'.($row+1), '水量(吨)');
        $currentSheet->setCellValue('N'.($row+1), '水费(元)');
        $currentSheet->mergeCells('O'.$row.':O'.($row+1));
        $currentSheet->setCellValue('O'.$row, '累计月运行费(元)');
        $row += 2;
        //写入记录
        $index_num = 1;
        $run_num = 0;
        $time = 0;
        $flow = 0;
        $save_water_num = 0;
        $save_water_time = 0;
        $dl_ele = 0;
        $zm_ele = 0;
        foreach($push as $name => $record){
            $currentSheet->setCellValue('A'.$row, $index_num++);
            $currentSheet->setCellValue('B'.$row, $name);
            $currentSheet->setCellValue('C'.$row, $record['save_water_num']);
            $currentSheet->setCellValue('D'.$row, $record['save_water_time']);
            $currentSheet->setCellValue('E'.$row, $record['run_num']);
            $currentSheet->setCellValue('F'.$row, $record['time']);
            $currentSheet->setCellValue('G'.$row, $record['flow']);
            $currentSheet->setCellValue('H'.$row, $record['dl_ele']);
            $currentSheet->setCellValue('K'.$row, $record['zm_ele']);
            $run_num += $record['run_num'];
            $time += $record['time'];
            $flow += $record['flow'];
            $save_water_num += $record['save_water_num'];
            $save_water_time += $record['save_water_time'];
            $dl_ele += $record['dl_ele'];
            $zm_ele += $record['zm_ele'];
            $row++;
        }
        //本年合计
        $year_ele = $dl_ele + $zm_ele;
        $currentSheet->setCellValue('B'.$row, '本年合计');
        $currentSheet->setCellValue('C'.$row, $save_water_num);
        $currentSheet->setCellValue('D'.$row, $save_water_time);
        $currentSheet->setCellValue('E'.$row, $run_num);
        $currentSheet->setCellValue('F'.$row, $time);
        $currentSheet->setCellValue('G'.$row, $flow);
        $currentSheet->setCellValue('H'.$row, $dl_ele);
        $currentSheet->setCellValue('K'.$row, $zm_ele);
        $currentSheet->getStyle('A1:O'.$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $currentSheet->getStyle('A1:O'.$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $currentSheet->getStyle('A1:'.'O'.$row)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        //设定封面
        $currentSheet->setCellValue('C8', date('Y年m月d日', strtotime($s)).'至'.date('Y年m月d日', strtotime($e)));
        $currentSheet->setCellValue('M8', $month_flwo);
        $currentSheet->setCellValue('M12', $flow);
        $currentSheet->setCellValue('M9', $month_ele);
        $currentSheet->setCellValue('M13', $year_ele);
        $currentSheet->setCellValue('L20', getNowDate_zhcn());
        $php_writer = new \PHPExcel_Writer_Excel5($php_excel);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        $out = substr($start,0,4).'年'.substr($this_month,5,2).'月调蓄池月运行月报表';
        header("Content-Disposition: attachment;filename=".$out.".xls");
        header('Cache-Control: max-age=0');
        $php_writer->save('php://output');
    }
}